Problem with VLOOKUP when i try to look for more than 1 criteria

[varun]

COLUMN 1 COLUMN2 TARGET cloumn3 column4 >TARGET
A 20 A 20 AA
B 30 B 30 BB
S 23 F 24 FF
D 23 D 23 DD
G 12 J 65 JJ
F 24 H 25 HH
D 23
A 20
S 23
D 20
F 24
G 12
H 25
J 65
K 65
L 43
J 65
G 12

In the above column named 'TARGET' i want to apply Vlookup. for eg:-( where
A=20 in COLUMN1 & COLUMN2, return 'AA' from '>TARGET' in 'TARGET')
similarly for all.
i hope i am clear with my question.


thanks,
varun

 

[Dave Peterson]

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

 

[Bob Phillips]

Try this

=INDEX(C2:C200,MATCH(1,(A2:A200=20)*(B2:B200=20),0))

it is an array formula, so commit with Ctrl-Enter-Shift