Search code examples
excelexcel-formulaworksheet-function

Excel - 2 tables in 2 worksheets (Find cell value in Table 2 if 2 cells from 2 tables match)

Here's a simple explanation of my current dilemma:

Table 1 (worksheet1): Column A contains Call Description (each call description has 3 time rates) Column B contains Time Rate (day, evening and weekend)

Table 2 (worksheet2): Similar layout where Column A and B contains Call Description and Time Rate but Column H contains the cost of that specific combination of Call Description and Time Rate.

So, I want to find out the cost of a specific call combination (Column H in Table 2) when the Call Description and Time Rate matches in both tables. Thus returning the cost value in a Column T in worksheet 1.

Essentially I'd want Column T in worksheet 1 to return the cost of the call combination.

Thanks

Is using Index and Match the right way to go?

Solution

  • You can use array formula INDEX(MATCH()):

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

    You'll have to apply it with Ctrl + Shift + Enter.