look up functions?

[Kelly]

Hi,
I need to match two criteria from table one and two (1, apple) and have it
pull a third piece of data from table one (juicy) into table two. You may
have multiple fruits attached to the account(1) so I need it to match both
and then give me the third.
1 apple juicy
1 banana yellow

Sorry if this is a confusing question!!
Kelly

 

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

 

[Sheeloo]

Sheet 1 has 1 apple jicy in Col A-C then Insert Col C and type
=A1&B1 and copy down till end of your data

If in Sheet2 Col A & B you have 1 and apple and want juicy in Col C then
type this in C1
=VLOOKUP(A1&B1, Sheet1!C,2,False) and copy down...

 

[Kelly]

hi Sheeloo,
Thanks for the help. Unfortunately, that didn't work. I got an N/A
message. I have the following formula in G1 of table two.

=VLOOKUP(A2&B2, master!A:G, 5, FALSE)

Any ideas why???

 

[Sheeloo]

Pl. check that the result of A2&B2 which will be AABB if A2 has AA and B2 has
BB exists in the column A of Master sheet...
If you see it then just to test enter in a blank cell in Col A of Master sheet
=Sheet1!A2&Sheet1!B2 and see...You should get 0. If you get that then enter
10 in corresponding E col. You should then get 10 in the A Col...
If this works then check your values in A... they might have an extra space
or something like that...