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

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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!
Rory ArchibaldCommented:
You should put your second match column in the other table. It doesn't really make any sense where it is.
Saurabh Singh TeotiaCommented:
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...

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Rob HensonFinance AnalystCommented:
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.

Rob H
agwalshAuthor 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
Saurabh Singh TeotiaCommented:
Their you go.. I made changes in the formula..use this and it will do what you are looking for...

Rob HensonFinance AnalystCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
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.
agwalshAuthor Commented:
Apologies for the delay. Thanks for your solutions..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.