Displaying wrong result while using Let function

[paradise2sr]

Hi,
I have used LET function in B5 but showi wrong result.My expected results are mentioned in F5:G8.

Couldn't figure out what is missing.

[wk9128]

worksheet or Tab name : Result

Cell B5 formula

=LET(z,Data!A4:C11,l,A5:A8,FILTER(FILTER(SORTBY(z,MATCH(INDEX(z,,1),l,)),SEQUENCE(ROWS(z))<=ROWS(l)),{0,1,1}))

[paradise2sr]

Thanx for the reply.
Can you exclude SORTBY function from above LET function.What it should be without it without any change in the result ?

Also,trying to use something like below function

XLOOKUP(A5:A8,Data!A4:A11,Data!B4:C11,0)

in it.

Is it possible.

[Bo_Ry]

Please try

=INDEX(Data!B4:C11,MATCH(A5:A8,Data!A4:A11,),{1,2})

[paradise2sr]

That seems to work.

I would like to ask one more thing.The lookup value (A5:A8) in my actual workbook is actually is extracted using something

TEXTJOIN(, 1, TEXT(MID(D6, ROW($D$6:INDEX($D$6:$D$969, LEN(D6))), 1), "#;-#;0;"))

When I use your formula,it gives #N/A Error.But when I copy paste and make it hardcoded and then converted into number then your formula works very fine.
Is,I need to wrap up by some other formula in it.

Kindly suggest me.How to resolve this kind of issue.

[AliGW]

Try using just this bit:

TEXT(MID(D6, ROW($D$6:INDEX($D$6:$D$969, LEN(D6))), 1), "#;-#;0;")

with the double unury:

--TEXT(MID(D6, ROW($D$6:INDEX($D$6:$D$969, LEN(D6))), 1), "#;-#;0;")

So you'll end up with this:

=INDEX(Data!B4:C11,MATCH(--TEXT(MID(D6, ROW($D$6:INDEX($D$6:$D$969, LEN(D6))), 1), "#;-#;0;"),Data!A4:A11,),{1,2})

Any good?

[paradise2sr]

I have used below as per your Post #6 in my real working workbook.

INDEX(Data!B4:C1604,MATCH(--TEXT(MID(D6, ROW($D$6:INDEX($D$6:$D$969, LEN(D6))), 1), "#;-#;0;"),Data!A4:A1604,),{1,2})

It is displaying error as #VALUE!.

I think I have to go by converting into number by doing hardcoded which is working very fine.

[AliGW]

Hard to say without seeing the workbook, but I see you've marked this as solved.

[paradise2sr]

Yes,marked it solved as in hardcoded number it works.

As I cannot disclose in publicly the workbook.But to let you know, that the number is extracted from other columns using as mentioned above function namely TextJoin and this is the lookup value.
Rest is the remaining story as stated above.