Solved Formula to lookup value

[kirana2014]

Please help me with excel formula i tried to xlookup value with below formula but it doesn't work please help me what mistake i made in this formula

=XLOOKUP("*("&D2&"*";$A$2:$A$4;$B$2:$B$4;;2)

 

[p45cal]

see how you get on with:

=INDEX($B$2:$B$4;MATCH(TRUE;ISNUMBER(SEARCH($A$2:$A$4;D2));0))

copied down.

If the numbers you're looking for are always the 2nd to 4th, then try a single-celled formula:

=INDEX(B2:B4;MATCH(VALUE(MID(D2:D4;2;3));A2:A4;0))

 

[kirana2014]

see how you get on with:

=INDEX($B$2:$B$4;MATCH(TRUE;ISNUMBER(SEARCH($A$2:$A$4;D2));0))

copied down.

If the numbers you're looking for are always the 2nd to 4th, then try a single-celled formula:

=INDEX(B2:B4;MATCH(VALUE(MID(D2:D4;2;3));A2:A4;0))

Dear p45cal,

Thank you is working
Well done!

​