Apr 29 2024 08:45 AM - edited Apr 29 2024 08:57 AM
Hello! I am having trouble with a xlookup formula and need gudiance in how to resolve.
Here is the issue:
In Column H I have the following formula H3 =MID(C3,21,3) which displays 131
Now on column N3, I have this formula: XLOOKUP(H3, Final!B4:B140, Final!C4:C140) but i am getting a #NA instead of getting "cash. Any thoughts? see attached.
Please attached file tab 8852
Apr 29 2024 08:57 AM
Most probably in H3 you have text "131" and lookup in column which have number 131. Try to use
=1*MID(C3,21,3)
Apr 29 2024 09:00 AM
Apr 29 2024 09:00 AM
There is no attachment.
The #NA is probably due to different formats/data types. Text vs. number.
Check with ISTEXT() or ISNUMBER().
Apr 29 2024 09:09 AM
When you do some arithmetic with texts which represent numbers, Excel automatically recognizes them as numbers and returns the result as number. Thus
=1 * "123"
="123" + 0
or
=VALUE("123")
all return number 123.
The option to attach the file
doesn't available here for everyone, only for the users who has some posting history. Alternatively you may share the file for everyone on OneDrive, Google Drive, whatever, and post here the link on the file.