Converting an HLOOKUP to Index and Match

[Melko]

Hi guys,

I've been working on someone else's spreadsheet just for setting up pivots and data displays. However, I realised it wasn't calculating the right numbers in all cases...and I've realised it's because they use an HLOOKUP and the whole left hand side only thing is affecting it.

It's so late and I haven't done index and match for a while. Anyone able to help convert the formula? It's =IF(AS$4=IFERROR(HLOOKUP(AS$4,$R5:$AC5,1)," "),1,"")

It just returns a '1' if the code in AS$4 (etc.) is also somewhere in $R5:$AC5. Any help would be so gratefully received.

Thanks in advance!
Mel

 

[mrhstn]

To get the same result you might use =IF(ISNUMBER(MATCH(AS$4, $R5:$AC5, 0)), 1, "")

 

[Melko]

Thank you SO SO SO SO much. This has worked a treat.