Formula Structure for combined VLOOKUP and INDEX/MATCH that returns N/A in VLOOKUP

[matratus34]

Hi
I’m lookingfor a bit of help with my formula please.

I’m trying toVLOOKUP to a table of customers and if they are in my table with a “Y” criteriain column H then I want the word “Exempt” putting in my cell.
If they don’thave a “Y” in column H then I want to do an INDEX and MATCH to a different dataset to find the value for the cell.
My problem isthat not all my customers are in the original table – if they aren’t then theVLOOKUP returns #N/A and doesn’t move onto the INDEX and MATCH part of theformula.

I’ve been playinground with IFERROR and ISNA but still can’t get it to work.
Any help isgreatly appreciated as always.


My currentformula is:

=IF(VLOOKUP(‘BASESHEET’B7,'Customer'!B:H,7,0)="Y","Exempt",INDEX('ALL PROJECTS'!$F$7:$W$237,MATCH('BASESHEET'!$B7,'ALL PROJECTS'!$B$7:$B$237,0),MATCH('BASE SHEET'!F$6,'ALLPROJECTS'!$F$6:$W$6,0)))

 

[matratus34]

I've managed to get this to work by starting the formula with =IF(ISNA(VLOOKUP('BASESHEET'B7
The formula then returns the INDEX and MATCH result first and the vlookup result after