Vlookup

[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

 

[Don Guillett]

Clarification?

 

[J.E. McGimpsey]

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

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

 

[Dave Peterson]

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.)

 

[immanuel]

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.

 

[shinydiamond]

Your Match formula is great and is what I used to get around th
inadequences of VLOOKUP,
However, try matching to an external file that you don't have opene
and it barfs.

See my new post :

http://www.excelforum.com/t168364-s


Any ideas?

:confused