| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and display
example;
sheet1;A2:A10 and sheet2;A2:A10 contain the same unique values sheet1;B2:B10 contains no unique values sheet2:B1 contains a value I need a formula that will return a match to correspond with sheet2;B1 and sheet1;B2:B10 and if found return it in sheet2;B2:B10 Bearing in mind that sheet1;A2:A10 and sheet2;A2:A10 must also match Thank you if you can be of help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match and display
Assuming you are dealing with numbers, I believe this will work (place into
Sheet2!B2): =SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),(Sheet1!$B$2:$B$10)) Formula looks up A2 in Sheet1, and if corresponding value in Sheet1 column B = Sheet2!B2, returns value from Sheet1 column B. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gootroots" wrote: example; sheet1;A2:A10 and sheet2;A2:A10 contain the same unique values sheet1;B2:B10 contains no unique values sheet2:B1 contains a value I need a formula that will return a match to correspond with sheet2;B1 and sheet1;B2:B10 and if found return it in sheet2;B2:B10 Bearing in mind that sheet1;A2:A10 and sheet2;A2:A10 must also match Thank you if you can be of help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match and display
Actually Luke M I should have stated text values not numbers
"Luke M" wrote: Assuming you are dealing with numbers, I believe this will work (place into Sheet2!B2): =SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),(Sheet1!$B$2:$B$10)) Formula looks up A2 in Sheet1, and if corresponding value in Sheet1 column B = Sheet2!B2, returns value from Sheet1 column B. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gootroots" wrote: example; sheet1;A2:A10 and sheet2;A2:A10 contain the same unique values sheet1;B2:B10 contains no unique values sheet2:B1 contains a value I need a formula that will return a match to correspond with sheet2;B1 and sheet1;B2:B10 and if found return it in sheet2;B2:B10 Bearing in mind that sheet1;A2:A10 and sheet2;A2:A10 must also match Thank you if you can be of help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match and display
Ah. Need to add a little more then...
=INDEX(Sheet1!$B:$B,SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),ROW(Sheet1!$B$2:$B$1 0))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gootroots" wrote: Actually Luke M I should have stated text values not numbers "Luke M" wrote: Assuming you are dealing with numbers, I believe this will work (place into Sheet2!B2): =SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),(Sheet1!$B$2:$B$10)) Formula looks up A2 in Sheet1, and if corresponding value in Sheet1 column B = Sheet2!B2, returns value from Sheet1 column B. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gootroots" wrote: example; sheet1;A2:A10 and sheet2;A2:A10 contain the same unique values sheet1;B2:B10 contains no unique values sheet2:B1 contains a value I need a formula that will return a match to correspond with sheet2;B1 and sheet1;B2:B10 and if found return it in sheet2;B2:B10 Bearing in mind that sheet1;A2:A10 and sheet2;A2:A10 must also match Thank you if you can be of help. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match and display
Not quite there yet Luke!
Let me try another example: Sheet1 and Sheet2; A2 = cat A3 = dog A4 = goldfish A5 = hampster Sheet1; D3 = food F5 = food Sheet2; B1 = food B2 to B5 will contain the formula Now the formula will look in sheet1 and will return food if applicable. The result for this example would be: sheet2; cat dog = B3 = food goldfish hampster = B5 = food "Luke M" wrote: Ah. Need to add a little more then... =INDEX(Sheet1!$B:$B,SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),ROW(Sheet1!$B$2:$B$1 0))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gootroots" wrote: Actually Luke M I should have stated text values not numbers "Luke M" wrote: Assuming you are dealing with numbers, I believe this will work (place into Sheet2!B2): =SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),(Sheet1!$B$2:$B$10)) Formula looks up A2 in Sheet1, and if corresponding value in Sheet1 column B = Sheet2!B2, returns value from Sheet1 column B. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gootroots" wrote: example; sheet1;A2:A10 and sheet2;A2:A10 contain the same unique values sheet1;B2:B10 contains no unique values sheet2:B1 contains a value I need a formula that will return a match to correspond with sheet2;B1 and sheet1;B2:B10 and if found return it in sheet2;B2:B10 Bearing in mind that sheet1;A2:A10 and sheet2;A2:A10 must also match Thank you if you can be of help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match and display
Thanks for the example.
In Sheet2!B2: =IF(COUNTIF(OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$A $2:$A$10,0),0,1,5),B$1)0,B$1,"") Inside the OFFSET function, the last number determines how many columns to check (currently set to look at B:F). This formula will work even if the two lists in A2:A5 are not in identical order. If your guaranteed that the lists will always be exactly the same, can use simpler formula: =IF(COUNTIF(Sheet1!$B2:$F2,B$1)0,B$1,"") Do either of these work for you? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gootroots" wrote: Not quite there yet Luke! Let me try another example: Sheet1 and Sheet2; A2 = cat A3 = dog A4 = goldfish A5 = hampster Sheet1; D3 = food F5 = food Sheet2; B1 = food B2 to B5 will contain the formula Now the formula will look in sheet1 and will return food if applicable. The result for this example would be: sheet2; cat dog = B3 = food goldfish hampster = B5 = food "Luke M" wrote: Ah. Need to add a little more then... =INDEX(Sheet1!$B:$B,SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),ROW(Sheet1!$B$2:$B$1 0))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gootroots" wrote: Actually Luke M I should have stated text values not numbers "Luke M" wrote: Assuming you are dealing with numbers, I believe this will work (place into Sheet2!B2): =SUMPRODUCT(--(Sheet2!$B$1=Sheet1!$B$2:$B$10),--(Sheet2!A2=Sheet1!$A$2:$A$10),(Sheet1!$B$2:$B$10)) Formula looks up A2 in Sheet1, and if corresponding value in Sheet1 column B = Sheet2!B2, returns value from Sheet1 column B. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gootroots" wrote: example; sheet1;A2:A10 and sheet2;A2:A10 contain the same unique values sheet1;B2:B10 contains no unique values sheet2:B1 contains a value I need a formula that will return a match to correspond with sheet2;B1 and sheet1;B2:B10 and if found return it in sheet2;B2:B10 Bearing in mind that sheet1;A2:A10 and sheet2;A2:A10 must also match Thank you if you can be of help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| display first or second match instead of #REF in INDEX ROW formula | Excel Worksheet Functions | |||
| Match Function And Display Row Number | Excel Worksheet Functions | |||
| Cross match data in Col A v/s Col B and display match in Col 3 | Excel Discussion (Misc queries) | |||
| Print out doesnt match display | Excel Discussion (Misc queries) | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
Linear Mode
