Xlookup with dynamic criteria

[indygo]

hi,
I couldn't find this in forum history. Any help is appreciated.
The question is very simple, why this type of formula doesn't work? I want to focus on the lookup_value part only:
Both A2 and B2 are types of ID numbers, and -1 is there to simply look one value below. It's a multiple search criteria which doesn't seem to accept any dynamic calculation.

=XLOOKUP($A2&$B2-1,

the same with alternative version with cell reference

=XLOOKUP($A2&($B2-D$1)

thanks
Indygo

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You cannot subtract 1 from a string, which is why it doesn't work.

 

[indygo]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You cannot subtract 1 from a string, which is why it doesn't work.

apologies, updated

 

[Fluff]

Thanks for that
It's not clear what you are trying to do, but as I said before you cannot subtract 1 from a string.
If you are trying to find a match & return the row below the match you will need to offset the return range by one row.

 

[indygo]

=XLOOKUP($B2&$L2-1,$B$2:$B$200000&$L$2:$L$200000,$J$2:$J$200000,"")
This is my entire formula, any recommendations how to replace this part to make it work?

 

[Fluff]

Without knowing what you are trying to do, it's very difficult to help.
Can you please explain & also provide some sample data along with expected results?