Search code examples
excelexcel-formulaexcel-2013vba

Find appropriate value from table

enter image description here

I have below table in Excel. I want to display appropriate value in "C9" from table. User can only change value of C7 and C8.
I am using =IF(AND(),) statement but it is way too long. Can you suggest me any other formula that I can use. I have tried VLOOKUP but it didn't work. Can I use INDEX and MATCH together? Can you guide me on that?

Solution

  • Probably better on superuser.com but the best way to do this is to learn to use index and match functions.

    =index($B$2:$F$5,match(c8,$a$2:$a$5,0),match(c7,$B$1:$F$1,0))

    Index takes an array and the row and column index you want.

    Match returns which cell the value is in an array.

    So match(c8...) returns 1 since it is the first value in the array.

    After the matches are done it becomes

    =index($B$2:$F$5,1,1)

    Which means take the first row and first column of that array.