MicrosoftYou may feel vindicated to know that I managed to fall into the 'parameter 4' trap. I was performing a 2D interpolation of gas density versus temperature (horizontal axis) and gauge pressure (vertical axis). Given a pressure, I need to return the next higher and lower values from the table in order to be able to interpolate the density. The formulas I needed were
= XLOOKUP( GaugePressure, PressureTbl, PressureTbl, , {-1;1} )
= XLOOKUP( Temperature, TemperatureTbl, TemperatureTbl, , {-1,1} )
but, sure enough, I forgot and put the match mode in the wrong place.
I had also encountered the problem of XLOOKUP not returning 'arrays of arrays' that you mentioned earlier. If I required a single 2D lookup, I could do it by range intersection using
= XLOOKUP( Temperature, TemperatureTbl, AirDensity, , -1 ) XLOOKUP( GaugePressure, PressureTbl, AirDensity, , -1 )
but XLOOKUP will not return the pairs of rows or columns that I need. Consequently, I reverted to INDEX/XMATCH to produce a 2x2 array of results
= INDEX( AirDensity, XMATCH( GaugePressure, PressureTbl, {-1;1} ), XMATCH( Temperature, TemperatureTbl, {-1,1} ) )
In the case of XMATCH, the match mode parameter is where I have come to expect it since there is no 'if not found' parameter. That set me up perfectly to fall into the XLOOKUP trap.
Slightly rueful but Wyn Hopkins could alway argue that, since I dug myself out of the hole, I do not require help in the form of any alternative parameter order.
