CODEWITHSUNDEEP
excel
vrghost
vrghost

Reputation: 1224

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

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

Upvotes: 1

Views: 170

Answers (1)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

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

Upvotes: 1

Related Questions