How can I use Excel VLOOKUP function based on specific text and not displaying all columns [duplicate]

How can I use VLOOKUP to display data from Sheet A to Sheet B based on one specific value in Sheet A cell D5. The specific value will be also located in Sheet B Cell A1.

I have tried using something like VLOOKUP($A2, sheet1!$A:$F, 1) but it has not help. Keep giving me error.

Sheet 1 (data)

Sheet 2 (result)

Answer

Given your current setup, in Result!B3 you can use:

=XLOOKUP(A1,Sheet!F:F,Sheet1!B:F)

This returns the whole row associated to the ID in A1, if you want to exclude the Filter column, you can try:

=INDEX(XLOOKUP(A1,Sheet!F:F,Sheet1!B:F),{1,2,3,5})