Vlookup Problem or other Solution needed

  • Thread starter Thread starter Akiono Wan
  • Start date Start date
A

Akiono Wan

Hi,

my Problem is that I have got a table in which Columns A and B
together are the primary key for Column C, which I want to refer to.
I tried to use vlookup but I can lookup only on one Column therefore I
found only the first hit but not the right reference, I would like to
find.

Here an Example

Column A Column B Column C
AEntry1 BEntry1 Text I found if vlookup on Column B or Column A
AEntry1 BEntry2 Text I found if vlookup on Column B but not on
Column A
AEntry2 BEntry1 Text I found if vlookup on Column A but not
Column B
AEntry2 BEntry2 Text I never found

I would like an vlookup over both Columns A and B to get Column C.
Is there any approach with vlookup or should I use something else?

Thanks for Help
Akion Wan
 
Akiono

I entered this data:

Product ID Batch Qty g234 z123
G234 Z123 84 84
H6657 Z435 25
FD675 XK980 24
G234 Z765 24

E1:F1 are the lookup values. In E2 I entered this Array formula.

=INDEX($C$2:$C$5,MATCH(E1&F1,A2:A5&B2:B5,0))

An array formula is entered Ctrl + Shift + Enter (CSE), is this OK?

Peter
 
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))
 
Back
Top