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
mtarler
Sep 28, 2022Silver Contributor
There are several options ranging from:
=INDEX(INDEX($O$4:$R$12,,IF(B3=“u”,2,IF(B3=“g”,3,4))),MATCH(J3,$O$4:$O$12,1))
to using new functions like XLOOKUP instead (which is more efficient and gives more options)
=XLOOKUP(J3, $O$4:$O$12, CHOOSECOLS($O$4:$R$12,SWITCH(B3,“u”,2,“g”,3,4)),"not found",-1,2)
note many variations in between are possible using SWITCH (or IFS) instead of cascading IF, using INDEX vs CHOOSECOLS or a number of other new functions like FILTER. But not know exactly what you are doing/need and what system you are running it is hard to recommend 1 way vs another.