Replace all IFERROR & INDEX & MATCH formulas with XLOOKUP

[Fabulist]

Is it possible to replace all formulas containing IFERROR, INDEX and MATCH formulas with XLOOKUP?

For example:

this "IFERROR(INDEX(TB!B3:L9999;MATCH(A112;TB!A3:A9999;0);10);0)"
to this "XLOOKUP(A112; TB!A3:A9999; TB!K3:K9999; 0)" <- (I assume this is a correct 1:1 replacement of the above)

and this "IFERROR(INDEX(TB!B3:L9999;MATCH(A112;TB!A3:A9999;0);11);0)"
to "XLOOKUP(A112; TB!A3:A9999; TB!L3:L9999; 0)"

and this "IFERROR(INDEX(TB!B3:L9999;MATCH(A112;TB!A3:A9999;0);2);0)"
to "XLOOKUP(A112; TB!A3:A9999; TB!C3:C9999; 0)"

and this "IFERROR(INDEX(TB!B3:L9999;MATCH(A112;TB!A3:A9999;0);3);0)"
to "XLOOKUP(A112; TB!A3:A9999; TB!D3:D9999; 0)"

and this "IFERROR(INDEX(TB!B3:L9999;MATCH(A112;TB!A3:A9999;0);6);0)"
to "XLOOKUP(A112; TB!A3:A9999; TB!G3:G9999; 0)”

and this "IFERROR(INDEX(TB!B3:L9999;MATCH(A112;TB!A3:A9999;0);7);0)"
to "XLOOKUP(A112; TB!A3:A9999; TB!H3:H9999; 0)"

Certain cells in the workbook contain multiple formulas like the above being added or subtracted.

A112 can be any cell or even a specific reference from TB!A3:A9999 such as "IFERROR(INDEX(TB!B3:L9999;MATCH("90.00";TB!A3:A9999;0);7);0)" or "IFERROR(INDEX(TB!B3:L9999;MATCH(90;TB!A3:A9999;0);7);0)". Some of MATCH's lookup_values are in quotes "" and some are not and this is necessary for INDEX-MATCH to work on this data. I do not know how to otherwise fix this issue that exists when the data codes lack period separators and exist as numerical only (note that 90.00<>90 and both will exist simulaniously).

Please excuse my formula separators, but I have “;” instead of “,” because of regional settings.

Is there a way to replace all formulas?

Thank you.

 

[arun_eagertolearn]

Hi

Yes, INDEX, MATCH can be replaced with XLOOKUP
In the examples that you have given, as the lookup value is on the left side of the array (A3:A9999), and the column to be matched is on the right (B3:L9999), a VLOOKUP also will work

Coming to the second part, I think the issue is between "90.00" (saved as a text/characters) v 90 (as numerals)
Otherwise, excel should treat 90.00 and 90 as the same

To work around this, you may convert all characters to numbers using VALUE (in case all the entries can be expressed as numbers)
OR
convert all numbers to characters using TRIM (in case there are genuine text fields, eg: "abc: in between)

XLOOKUP(VALUE(A112); VALUE(TB!A3:A9999); TB!H3:H9999; 0)" or
XLOOKUP(TRIM(A112); TRIM(TB!A3:A9999); TB!H3:H9999; 0)"

 

[Fabulist]

I see, thank you. I will test if adding VALUE/TRIM to the entire array affect the workbook's performance and solve this issue.

Any suggestions on how to mass replace the IFERROR & INDEX & MATCH formulas with XLOOKUP in the workbook while retaining references?

 

[arun_eagertolearn]

I think the easiest way would be using the find-replace feature in excel

(please save a copy the current workbook before doing this)

please do the following sequences

IFERROR-->XLOOKUP (IFERROR to be replaced with XLOOKUP)
INDEX(TB!B3:L9999; -->blank
MATCH(-->blank

Then individually

; 0); 10)-->; TB!K3:K9999)
; 0); 9)-->; TB!J3:J9999)
; 0); 8)-->; TB!I3:I9999) etc.

 

[Fabulist]

Excel won't let me replace the formulas like this, unfortunately. Please note that the above is merely an example, these formulas are found following an equal = sign or +-.