LOOKUP Issues

Hi Experts,

I have a table format with numbers stored as text and number to do lookups.
Please can you help me with this small sample, so that I can take this to my work]

-- Sample file Attached.
I am trying to do XLOOKUP and VLOOKUP and tried with concatenating lookup value with " " but still did not get desired result

2020-12-23_14h00_53.png

The name of the table is Test.


XLOOKUP(F2 & "",Test[[#All],[ID]:[ID2]],Test[[#All],[Flag]]) Does not work
XLOOKUP($F$2&" ",Test[ID],Test[[Flag]:[ID2]]) Does not work
XLOOKUP($G$2,Test[ID],Test[[Flag]:[ID2]]) Working
VLOOKUP(F2,Test[[ID]:[ID2]],3)
VLOOKUP(F2&" ",Test[[ID]:[ID2]],2) Wrong and Does not change
VLOOKUP($G$2,Test,2) Working

Your help in both these functions and some explanation or links why it is not working will be appreciated.

@wk9128
Thanks a lot. Oh I am in office and using office 365. I have just edited the first post and attached the file. (sorry, I missed that)
Note: xlookup when for test, changed the format, worked and so is vlookup

Let me try github:https://github.com/acerNZ/PowerBI.git
filename: LookupIssues.xlsx

Please can you confirm, if you can access the file.

Best Regards

I am not using 365, but this is some info that may help:
to lookup a Value and Value-stored astext, there are 2 options:

1)
unique to value
F2 to value: F2+0 (F2 can be either value or text)
Column A4:A15 to value: A4:A15+0
Then match: =MATCH(F2+0,INDEX(A4:A15+0,),0)
Then get "Flag":
=INDEX(B4:B15,MATCH(F2+0,INDEX(A4:A15+0,),0))

2)
Unique to text
F2&"" (F2 can be either value or text; "" is null with lenght=0, not " "(lenght=1))
A4:D15&""
Then:
=VLOOKUP(F2&"",INDEX(A4:D15&"",),2,0)
(INDEX to avoid array formula, without it, must be confirmed with Ctrl-shift-enter)

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)