Formula - match going down, then across

[The Ruff Report]

Not sure if I'm wording the title great.

I want to be able to input Item# in cell J1, Price in cell J2, and have the Rebate associated with that price to return in cell J3.

In other words, find the Item# in column A, then go across that row until the price is matched. The price could be in column B, column N, column Z etc.

 

[Cubist]

Try:

=LET(f,FILTER(B2:G15,A2:A15=J1),INDEX(f,XMATCH(J2,f)+1))

 

[The Ruff Report]

Works perfect Cubist. Took me a few minutes to adapt to my real world file. Thank you very much.

I have never used LET before. Sigh....always so much to learn

 

[Joe4]

The LET function actually isn't necessary. It just allows you to store a calculation as a variable in your formula to avoid repetition.
It helps keep the formula shorter, by removing redundant calculations.

See: LET function - Microsoft Support