Vlookup

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello All,

Using Excel XP:

I have a lookup table that has 3 colums of information

eg.

A B C
1 4 1 3
2 4 2 2
3 4 3 1
4 4 4 0
------------------------------
5 4 3

=VLOOKUP($A5,Database,3,0) in VLOOKUP it takes the data in column A to
return C but I would like VLOOKUP to take the data in Column A & Column B
then return the value in C.

How would I set up the formula to do that , if it's possible.

Thank you,

Michael
 
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX($C1:$C4,MATCH($A5&$B5,$A1:$A4&$B1:$B4,FALSE))
 
maybe:

=INDEX($C$1:$C$4,MATCH(A5&CHAR(1)&B5,$A$1:$A$4&CHAR(1)&$B1:$B$4,0))

But instead of just hitting enter, hit ctrl-shift-enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
Or, if you know you won't have duplicates:

=SUM(IF((A1:A4=A5)*(B1:B4=B5),C1:C4,0))

(Also array-entered)

If you don't know whether you have duplicates, you could MAX or MIN instead
of SUM...

/i.
 
Back
Top