IF(VLOOKUP) vs INDEX(MATCH) to find values based on multiple criteria

Question

I'm trying to perfect one, but so far both formulas do not return what i'm after. I am trying to create a formula to return a cell in column FHE!E:E such that FHE!C:C matches DS!C5 and DS!G5="text".

I have tried 2 methods, IF(VLOOKUP):

=IF(DS!G5="text",VLOOKUP(DS!C5,FHE!A:Z,5),"")

This produces the same number (-81) for all cells where DS!G5="text" but -81 is not the value it should bring back from FHE!E:E

Also INDEX(MATCH):

=INDEX(FHE!E:E,MATCH(DS!C5 & DS!G5,FHE!C:C & "text"))

This just produces #VALUE

Any help on where I might be going wrong would be greatly appreciated

Answer 1

Make sure your lookup table starts with the left most column being the one you want to lookup from. Also add an additional argument to the lookup for exact match, i.e put ,0 at the end of your Vlookup bracket. See if that works and let me know! :)