Forum Discussion
Help with formulas
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
4 Replies
There is no attachment.
The #NA is probably due to different formats/data types. Text vs. number.
Check with ISTEXT() or ISNUMBER().
Most probably in H3 you have text "131" and lookup in column which have number 131. Try to use
=1*MID(C3,21,3)
- Sergei--Thank you! This resolved the issue. For my FYI, why does it work but multiply it by 1? Also, this is my first time using this thread. How does one upload an Excel file for illustration purposes?
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.
