Hi,
I'm can't seem to fix the formula getting #n/a data mismatch. Looking up date base on employee number (text) finding code and retrieve that code to lookup the value for that code in a second table.
=VLOOKUP(VLOOKUP(A8,TL_LOOKUP,3,0),WC_LOOKUP,2,0)
Hi, welcome to the forum
your lookup range is only the 1st 2 rows of your 3-row table
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
is this what you are looking for? =IFERROR(VLOOKUP(A8,TL_LOOKUP,3,0),VLOOKUP(A8,WC_LOOKUP,2,0))
but it still returns #N/A because it appears your TL_lookup does not extend far enough down col A to get that info.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Range for TL_LOOKUP is wrong.
It should be A3:C5.
You have it as A3:C4
1. Click on the * Add Reputation if you think this helped you
2. Mark your thread as SOLVED when question is resolved
Modytrane
I did change the cell to include the last record entered If I manually type the numbers in this formula this works. However, the data is pulled from a database in text causing the data mismatch.
You are specifying the data from a named range - the named is 1 row short
JOHNSON,T MICHAELS,K SIMPSON,J
TL_LOOKUP in your file has the range
=Sheet1!$A$3:$C$4
it needs to be
=Sheet1!$A$3:$C$5
you can do things to change items from text to numeric if there is a problem.
BUT, I put this formula in an empty cell =A8=A5 and it returned true so that means the 65 in A8 and the 065 in A5 match. And clicking on A5 it shows that it has "special" formatting that is showing it with a leading zero.
using a multiplication of *1 can change a cell from text to numeric such as =C3*1 will take the 5606 from text to numeric.
But a text will also work in the formula I gave you, so if you put =c3 into A8, it will return the value from the Admin tab.
I did the exact same thingOriginally Posted by Sambo kid
I have retried the raw data from SQL server as the data is populated to spreadsheet; still getting #n/a error
Now the range is not wide enough. The inner VLOOKUP is looking into col 3, but the range only has 2 cols
Also, you now DO have text strings in your data, but are trying to find a numeric
=IFERROR(VLOOKUP(""&A8,TL_LOOKUP,3,0),VLOOKUP(A8,WC_LOOKUP,2,0))
=A8=A3 returns TRUE, so the VLOOKUP should work. The named range references the correct array. What are we missing?
EDIT: Ugh! Missed that. As Ford says, the range needs to be one column wider.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks that worked!
well one problem is that this portion of the formula is wrong...
VLOOKUP(A8,TL_LOOKUP,3,0) your TL_LOOKUP range is A3 to B5 but your vlookup is telling the formula to go over 3 columns to column C but that is outside of the range so that will return a NA. you need to correct your range.
BTW, none of those replies were here when I started typing.
again, too slow.
@ Sambo, been there, done that
@ Ali, I almost missed hat 1 too
After reviewing, the formula retrieve the WC_code correctly, we are trying the get the corresponding value for that code.
This is the formula you need:
=IFERROR(VLOOKUP(VLOOKUP(""&A8,TL_LOOKUP,3,0),WC_LOOKUP,2,0),"")
However, there are leading spaces in the first column of the admin lookup table, which need removing, then it will work.
My formulas to remove leading zeros have not worked.
What does this mean? Are you still in need of help?
Yes, I can't get the formula to work. I've tried several methods to remove leading zeros and still no luck.
What did you try?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks