Forum Discussion
Instead of XLOOKUP function what combination of function can be used ?
Yes. Actually XLOOKUP was a welcome addition to replace the traditional INDEX( MATCH() ) combination that was previously required in certain cases. In addition to replacing the need for the INDEX - MATCH combination, XLOOKUP also has IFERROR (technically better since it is specifically IF_NOT_FOUND and therefore you don't accidentally mask errors for other things), and new and different search capabilities and is more efficient.
But all that said, yes, you can use
=IFERROR( INDEX( output_array, MATCH( search_item, search_array, limited_search_options) ), if_not_found)
as a close but still not as good as XLOOKUP option
of course you still have other LOOKUP options including LOOKUP, VLOOKUP, and HLOOKUP. In some cases these have 'simplier' entry but still lack a number of features and benefits of XLOOKUP. For example you have VLOOKUP( value, table, index) so something like =VLOOKUP( a1, b1:e1000, 4) instead of =XLOOKUP( a1, b1:b1000, e1:e1000) which is slightly easier to type (7 characters in this case) but having the ability to include forward, backward, wildcard search and if_not_found value usually outweighs those 7 characters.
and this doesn't even get into adding in named ranges and structured table references that will work better in XLOOKUP to improve readability:
=XLOOKUP ( name, MyTable[Names], MyTable[eMail] )
this is pretty clearly a lookup for 'name' in MyTable and returning the corresponding e-mail
the equivalent in VLOOKUP might be:
=VLOOKUP ( name, MyTable, 4 )
and we can see it will look up 'name' in MyTable and return the 4th column, whatever that is.