Forum Discussion
HopeGregory
Sep 28, 2022Copper Contributor
Converting Formulas
I was wondering how to convert this VLOOKUP formula =VLOOKUP(J3,$O$4:$R$12,IF(B3=“u”,2,IF(B3=“g”,3,4)),TRUE) into an INDEX MATCH formula
JMB17
Sep 29, 2022Bronze Contributor
If it has to be index/match, then you could also try:
=INDEX($P$4:$R$12, MATCH(J3, $O$4:$O$12), IFNA(MATCH(B3, {"u","g"}, 0), 3))
The only caveat is that if is possible someone might insert a column in between columns P and R, then will it still return the desired result (because your column reference is based on position and your columns could get shifted over).
If you want the return value to be unaffected by inserting columns, then you could try (note two commas before IFNA function):
=INDEX(($P$4:$P$12, $Q$4:$Q$12, $R$4:$R$12), MATCH(J3, $O$4:$O$12), , IFNA(MATCH(B3, {"u","g"}, 0), 3))