• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Double HLookup?

Hi all,

I am trying to use two HLookups to find data in a table based on two criteria. I am setting up a series of front sheets to read my data, and need a a formulae that will do a hlookup on the date, then hlookup on the team, and then look down to find the relevant number.

As an example I have included an example spreadsheet. On Sheet 1, I would like C3 to show the Total Incoming from Sheet 2 for Team 3. I have set up my front sheet to include a drop down box that changes the dates in C2 to F2 to the chosen week. So C3 would need to search for the date that is chosen, then choose the correct team, and then find the relevant number.

I feel this may be achievable with two H Lookups, or maybe an Index Match Match?
 

Attachments

  • chandoo example 1.xlsx
    10.9 KB · Views: 6
Hi @Mike Langlois

You can use this formula in Sheet1, Cell C3 (I supose all teams in all your tables was in the same order - Team 1 to Team 6):
=INDEX(OFFSET(Sheet2!$B$20:$G$20,,MATCH(C2,Sheet2!$B$1:$P$1,)-1),MATCH($B$1,Sheet2!$B$2:$G$2,))

And copy it to the right.
Please comment! Blessings!
 
Hi John,

This is perfect, thanks very much!

I have never used Offset before? Would you be able to describe the process the formulae goes through, so I can understand it better?

Many thanks,
Mike
 
Back
Top