Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions |