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

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

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