Excel: search two columns against another two columns to return a value fron a third

Question

Got a bit of a pain. So got an interesting issue, basically. Have a long list of entities (200 plus) and I need to match them against a code which I have in another list. So from the entity list, I have the name and country of the entity (Name in column A, country in column D), I need to populate Column F with the code from the other list, or add unknown if a code cant be found.

So, tried to build the query by using the & operator So =MATCH(A2&D2 to use as key, giving me a value like 'cool companyUNITED KINGDOM'. In the second list (imported to sheet 2) contains the following columns Code Name Country

So I want to search an array where Name and country have been combined:

=MATCH(A2&D2,Sheet2!B2:B99999&Sheet2!C2:C99999,0)

I then try to get the index back, so my complete list looks like

=INDEX(Sheet2!A2:C99999, MATCH(Sheet2!A2&Sheet2!D2,Sheet2!B2:B99999&Sheet2!C2:C99999,0))

And all I get back is #Value

Any suggestions

Edit: More infor

So sheet one looks like this (Its column C I need to populate from the code in column A, sheet two)

Entity name Status  GIIN    Country
Ben Dist Ltd    NFFE    N/a UNITED KINGDOM
Karamara Sdn Bhd    PFFE    N/a MALAYSIA
Farbion Trade (Curacao) N.V.    LFFI    N/a 
Tentorim (International) B.V.   LFFI    N/a NETHERLANDS
Catamo B.V. TLTD    N/a NETHERLANDS
Ben Dist Deutschland GmbH   FLTD    N/a GERMANY
Ben Dist Investments B.V.   PFFE    N/a NETHERLANDS
Ben Dist Limited    TLTD    N/a UNITED KINGDOM
Complete Solution Service Limited   GLRS    N/a UNITED KINGDOM
BDLT S.A. de C.V.   TLTD    N/a MEXICO
Telsa Telco Services    SLTD    N/a CHILE

And the second list will look like this

GIIN    FINm    CountryNm
AAAUG3.99999.SL.764 Asset Plus HSI Fund THAILAND
AABEIL.99999.SL.528 Gresham Capital CLO II B.V. NETHERLANDS
AAB36F.99999.SL.470 Maitland Malta Limited  MALTA
AACRQK.99999.SL.756 BBGI GROUP SA   SWITZERLAND
AADAD7.99999.SL.528 E-MAC DE 2009-I B.V.    NETHERLANDS
AADDBX.99999.SL.060 GWD Limited BERMUDA
AAE9W5.99999.SL.764 Bualuang Money Market RMF   THAILAND
AAGH8E.99999.SL.276 Sparda-Bank Baden-Wuerttemberg eG   GERMANY
AAGR6U.99999.SL.438 Konsolidationsanstalt   LIECHTENSTEIN
AAGWV3.99999.SL.360 BATAVIA PROTEKSI PRIMA 18   INDONESIA
AAGXH0.99999.SL.136 Monarch Capital Partners Ltd    CAYMAN ISLANDS
AAHY1V.99999.SL.158 Pingtung County Farmers' Association    TAIWAN
AAH0IZ.99999.SL.136 Diversified Absolute Return Fund    CAYMAN ISLANDS

Few days ago I have asnwered a question and I think the solution could be also useful for you stackoverflow.com/questions/25617401/… (the array formula) — Vojtěch Dohnal
– Vojtěch Dohnal, Commented Sep 3, 2014 at 11:37
If you show a little example of your other list and few sample entities then your problem would be easier to comprehend. — Vojtěch Dohnal
– Vojtěch Dohnal, Commented Sep 3, 2014 at 12:31
From MATCH I get absolutely nothing. But have a sneaky suspicion that the problem is with how I enter a formula. There is something about entering Ctrl+Shift+Enter, but on a mac that does nothing. — vrghost
– vrghost, Commented Sep 3, 2014 at 12:53

Vojtěch Dohnal · Accepted Answer · 2014-09-03 19:13:28Z

1

I suggest that you use following array formula:

= IFERROR(INDEX(List,SMALL(IF((INDEX(List,,2,1)=A2)*(INDEX(List,,3,1)=D2),ROW(List)-MIN(ROW(List))+1,""),1),1,1),"N/A")

To enter array formula in Windows use Ctrl+Alt+Enter. On Mac keyboard use Command+Enter. Then drag the formula downwards.

In this formula I have used named range List, which is equivalent to your Sheet2!$A$2:$C$99999. Named ranges make complicated formulas more readable and flexible. If you do not want to use named ranges just replace List with Sheet2!$A$2:$C$99999.

=IFERROR(INDEX(Sheet2!$A$2:$C$99999,SMALL(IF((INDEX(Sheet2!$A$2:$C$99999,,2,1)=A2)*(INDEX(Sheet2!$A$2:$C$99999,,3,1)=D2),ROW(Sheet2!$A$2:$C$99999)-MIN(ROW(Sheet2!$A$2:$C$99999))+1,""),1),1,1),"N/A")

It works if your sheets look as follows:

Sheet1

Sheet2

edited Sep 3, 2014 at 19:13

answered Sep 3, 2014 at 19:07

Vojtěch Dohnal

8,1123 gold badges53 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vrghost Over a year ago

VDhonal: You ROCK This did the trick, and I think it worked on the whole area, even if I am not 100% certain why it worked, but that is what I will try to figure out now. Thank you so very much.

Vojtěch Dohnal Over a year ago

@vrghost If you want to understand the array formula, just select different pieces of it, like INDEX(List,,2,1) and press F9 and it will show you the array of values, then it is easier to debug and understand it. If it works for you, you can also mark the answer as accepted.

Collectives™ on Stack Overflow

Excel: search two columns against another two columns to return a value fron a third

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related