look up functions?
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 |
look up functions?
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)) Kelly wrote: 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 |
look up functions?
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:D,2,False) and copy down... "Kelly" wrote: 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 |
look up functions?
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" wrote: 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:D,2,False) and copy down... "Kelly" wrote: 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 |
look up functions?
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... "Kelly" wrote: 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" wrote: 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:D,2,False) and copy down... "Kelly" wrote: 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 |
All times are GMT +1. The time now is 01:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com