CODEWITHSUNDEEP
excel
NoviceStat
NoviceStat

Reputation: 45

Match values of two columns with other two columns and return value in excel

I have two tables (Table A and Table B). Table A includes name of state and country number in first two columns and specific value corresponding it (column 3) (n=300). Table B also has name of state and country number with several repetition (n=5425). I want to match column 1 and column 2 of Table A with Column 1 and Column 2 of Table B and copy the value from column 3 of Table A in Column 3 of Table B in excel.

Upvotes: 0

Views: 316

Answers (1)

Tim Wilkinson
Tim Wilkinson

Reputation: 3791

If you are able to use a helper column, in Table A Column D put =A1&B1 if its an actual table it should autopopulate, otherwise fill down for all your data), then using INDEX MATCH in Table B, put the following in C1

=INDEX(Sheet1!C:C, MATCH(Sheet2!A1&Sheet2!B1, Sheet1!D:D, 0))

Alternatively you can match multiple criteria with an array formula, so use the following,

=INDEX(Sheet1!C:C,MATCH(Sheet2!A1&Sheet2!B1,Sheet1!A:A&Sheet1!B:B,0))

And enter by hitting CTRL + SHIFT + ENTER.

Upvotes: 1

Related Questions