We help IT Professionals succeed at work.

guidelines on using match to compare two lists that are not the same height

agwalsh
agwalsh asked
on
hi Folks
Just wondering about this....I've attached a file with two lists - they are not the same height. My issue is this...the item in cell g23 is actually in both but it's coming up as a Value message...presusmably because the two lists are not the same height...any thoughts/suggestions
EE-match-list-not-same-height.xlsx
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Yes - I have no idea what you mean.

Cell G23 is trying to compare A23 to column D. But there is nothing in A23, so naturally it is returning #VALUE!
Most Valuable Expert 2011
Top Expert 2011

Commented:
You should put your second match column in the other table. It doesn't really make any sense where it is.
Top Expert 2015
Commented:
You have applied the match formula incorrectly that's why it's happening...

In match formula you take the cell first and then the range and then the 0 which is for exact match..

If you change the formula to...

=MATCH([@[Cost Centre]],CostCentre[Cost Centre],0)

Open in new window


it will work which is you formula in column1..

Now i'm not sure about what you are trying to do here..if you can help me understand what you are trying to do..may be i can help you with the same...

Saurabh...
Rob HensonFinance Analyst

Commented:
Assuming I have the MATCH direction correct, the following works:

=MATCH([@[Cost Centre]],CostCentre[[#All],[Cost Centre]],0)

This matches the contents of column D with column A; for G23, looking at D23 it retruns 17, the relevant row from column A.

Thanks
Rob H

Author

Commented:
hi, what I am trying to get is the following...In Column 1 (D) what I want the formula to do is to look up the value in D3 (2111103), and then to look at the matching column in the table starting in a2 (called Cost Centre) and return the position/N/A if it's not there. I've amended the formula in Column F as per your suggestion and this now works as required i.e. it's showing up washing machine (1111111) as missing from the list N/A i.e. that it's not in the Cost Centre table.  Thank you :-)  Now in Column G (2nd comparison) I want it to do the same but in the other direction i.e. Look up a3, match to column D and return an N/A if there is no match. At the moment (see attached file) it's giving me an NA where I can see a match..e.g. 2111110 is in both lists but the Comparison 2 formula (as I have it) is showing N/A. Ditto with 2111118
EE-match-list-not-same-height-vers-02.xl
Top Expert 2015
Commented:
Their you go.. I made changes in the formula..use this and it will do what you are looking for...

Saurabh....
EE-match-list-height-vers-02.xlsx
Finance Analyst
Commented:
Your #N/A in G3 is because you are looking for 9999999 (A3) in column D and it isn't there. Likewise, #N/A in G18 is because you are looking for 8888888 (A18) in column D and its not there.

The #VALUE! error in G23 is because there is no value in A23 for it to look for.

All of those results seem correct for what I can see.

Maybe it would be easier to understand if you inserted a column into the table in columns A:B and use that for the second comparison.

Thanks
Rob
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Saurabh Singh Teotia's comment #a40670385

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Author

Commented:
Apologies for the delay. Thanks for your solutions..