Multiple Criteria: IF 2x 2-Column Matches then show corresponding value in parallel row (two sheets)

[idrisjones]

Hi there,

I'm wanting to create a formula that retrieves a unique cell value if multiple criteria are met.

E.g. If 'Interface'!H8 matches item in 'ReferenceList'!ColumnT3:T245 & 'Interface'!I8 matches item in 'ReferenceList'ColumnU3:U245 then show corresponding value in 'ReferenceList'!ColumnV3:V245.

I've played with VLOOKUP, INDEX, MATCH, IF mainly, but gotten no where that I can apply it to all cells in Column J in 'Interface'.

Any idea's?

Cheers,
Idi

 

[steve the fish]

See if this does it

=INDEX(ReferenceList!V3:V245,MATCH(1,INDEX((ReferenceList!T3:T245=Interface!H8)*(ReferenceList!U3:U245=Interface!I8),0),0))

 

[idrisjones]

Thank you for your help - This was near perfect!

The solution was

=INDEX('Reference List'!$V$3:$V$245,MATCH(1,('Reference List'!$T$3:$T$245=Table7[@[Category 1]])*('Reference List'!$U$3:$U$245=Table7[@[Category 2]]),0))