Double Lookup

[Teddy-B]

I have to lookup in two ways and don't know how. I need to lookup a certain
date that is always in B5 but that may change. and a certain name that is
always in A5 but that may change. The list shows names and amounts assigned
to the point where name and date meets and I need the amount. For example in
cell B10 the amount is $5. Cell B5 shows 10-1-2009 and Cell A5 shows james.
So I need to look up that james got $5 on the date 10-1-2009. The report is
weekly so the pay period ending will change every two weeks. That is where my
trouble is.

Thanks for your help!

 

[Dave Peterson]

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

Look at example #3.

 

[Sean Timmons]

you can use a SUMPRODUCT...

Sounds like A5 and B5 are entered by you, then you want to look up these
values in, say, rows 6 through 100?

=SUMPRODUCT((A6:A100=A5)*(B6:B100=B5)*C6:C100)

would return the date that cell A5 and B5 match up with the table.

This assumes you only have one instance of A5 and B5 in yoru table.

If you can have the same person with the same date, you will get the sum of
amounts on that date for that person...

 

[Teddy-B]

Thank for your time. In this casde I need to look at another sheet and find
the date (B3:B33) that matches B5 on the current sheet. When the date data
matches, then it should be brought over. Would I do SUMPRODUCT, then LOOKUP
or LOOKUP first, then SUMPRODUCT?
Would this be a case for V or H lookup?