locked
Match data based on two cell values RRS feed

  • Question

  • Hello there,

    can we use Vlookup to find data match baed on two cell values.

    Like I have data with the following format

    Sheet 1 contains main table with the following columns 

    Column A - Company Name

    Column B - Years

    Column C - Assets

    Column D - EBIT

    Sheet 2 (need to pull Data for)

    Column A - Company Name

    Column B - Years

    Column C - Need to pull data here for assets based on Column A & Column B of Sheet2 like if Sheet2 A1 & B1 match with the A1 & B1 of sheet1 then Pull the data in C1.

    Please advise.

    Regards, Sara

    Thursday, September 10, 2020 12:33 AM

Answers

  • On Sheet2, in cell C2 as an array formula confirmed with Ctrl+Shift+Enter:

    =IFERROR(INDEX('Sheet 1'!$C$2:$C$200,MATCH(1,('Sheet 1'!$A$2:$A$200=A2)*('Sheet 1'!$B$2:$B$200=B2),0)),"")

    Change Sheet 1 to the actual name of the first sheet, and adjust the ranges if needed.

    Then fill down.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by SaraPhdNZ Sunday, September 13, 2020 9:44 PM
    Thursday, September 10, 2020 2:39 PM

All replies

  • The easiest method of doing this is to add a "Helper column" and then concatenate the Company Name and Years in the "Helper column" then look up on the Helper column. The Lookup Value can be concatenated in the Vlookup Formula.

    You should be able to hide the "Helper column".


    Regards, OssieMac

    Thursday, September 10, 2020 7:07 AM
  • Hello OssieMac,

    Thanks for your response on this but I am not familiar with this Helper Column thing and would appreciate if you can share any document or video link that can explain this excel tool. 

    Regards,,Sara

    Thursday, September 10, 2020 10:25 AM
  • On Sheet2, in cell C2 as an array formula confirmed with Ctrl+Shift+Enter:

    =IFERROR(INDEX('Sheet 1'!$C$2:$C$200,MATCH(1,('Sheet 1'!$A$2:$A$200=A2)*('Sheet 1'!$B$2:$B$200=B2),0)),"")

    Change Sheet 1 to the actual name of the first sheet, and adjust the ranges if needed.

    Then fill down.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by SaraPhdNZ Sunday, September 13, 2020 9:44 PM
    Thursday, September 10, 2020 2:39 PM