Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Backwards? lookup function
I have two lists of items. The first is, for example 50 items - List A. I
want to look at another list (shorter, say 10 items - List B). I want to see what items in List A have a matching item in List B and return a value in that row. I have not been able to match items from lists of different sizes, unless List A is shorter than List B, rather than the other way around? Is there a way I can do this? Thanks for your help -- Cards Fan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Backwards? lookup function
Select the range of cells where you want the values from ListA to appear
(this should be at least as large as the larger of ListA and ListB), type in the following formula and press CTRL SHIFT ENTER rather than just ENTER. If you do this properly, Excel will enter the formula into all selected cells and display the formula enclosed in curly braces { }. =IF(NOT(ISERROR(MATCH(ListA,ListB,0))),ListA,"") See www.cpearson.com/excel/ArrayFormulas.aspx and www.cpearson.com/excel/ListFunctions.aspx -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "cardsfan" wrote in message ... I have two lists of items. The first is, for example 50 items - List A. I want to look at another list (shorter, say 10 items - List B). I want to see what items in List A have a matching item in List B and return a value in that row. I have not been able to match items from lists of different sizes, unless List A is shorter than List B, rather than the other way around? Is there a way I can do this? Thanks for your help -- Cards Fan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Backwards? lookup function
Thank you. I will try this. I think it will work for what I want to do.
-- Cards Fan "Chip Pearson" wrote: Select the range of cells where you want the values from ListA to appear (this should be at least as large as the larger of ListA and ListB), type in the following formula and press CTRL SHIFT ENTER rather than just ENTER. If you do this properly, Excel will enter the formula into all selected cells and display the formula enclosed in curly braces { }. =IF(NOT(ISERROR(MATCH(ListA,ListB,0))),ListA,"") See www.cpearson.com/excel/ArrayFormulas.aspx and www.cpearson.com/excel/ListFunctions.aspx -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "cardsfan" wrote in message ... I have two lists of items. The first is, for example 50 items - List A. I want to look at another list (shorter, say 10 items - List B). I want to see what items in List A have a matching item in List B and return a value in that row. I have not been able to match items from lists of different sizes, unless List A is shorter than List B, rather than the other way around? Is there a way I can do this? Thanks for your help -- Cards Fan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel is Backwards! | Setting up and Configuration of Excel | |||
Excel is Backwards! | Excel Discussion (Misc queries) | |||
populate backwards | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Backwards lookup | Excel Worksheet Functions |