I am distributed salary to several dimensions, to find out cost by tasks.
I am try to split dimension f.x 10203040 in 10 20 30 40 where first set of number is for group A of tasks, next two for group B of tasks etc.
I use left, and mid to split.
Next I want to use vlookup to make my pivot more readable change first set of number (10) to name (Group1)
I get #N/A
Any suggestions to solve it?
classic vlookup issue
Numbers are formatted as text (or vice versa)
if you type the number into a cell it becomes a number format which is why your vlookup works
if you use a LEFT formula it automatically converts it to a text
either change your lookup table to be text format OR change the output of the left formula
ive uploaded your spreadsheet
the left hand column changes the output of the left formula be using VALUE formula in addition to left
the right hand column i converted your lookup TABLE to be TEXT (note the green triangle on top left hand corner)
Last edited by humdingaling; 04-12-2018 at 08:11 PM.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
I came to the same conclusion as humdingaling
I choose to adapt the vlookup formula to recognize col b and c as numbers rather then text..
see attachment for changes I made in formula's
Hi,
I agree with humdingaling above. Your top table has Text when you did the Left and Mid formulas. You are trying to find those Text in the numbers below for a lookup. Do this to fix your problem.
Type a ZERO in a cell and copy it into the clipboard using Ctrl-C. Then select all those text from C8:F21 and right click on the selection. Click on "Paste Special..." and click on the Add radio button. This will add zero to those text and make them numbers and your work is done.
https://www.techrepublic.com/blog/mi...ting-in-excel/
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi,
Yes this was classic problem. I knew this was some text/number issue, but I didn't find the answer.
Value is the key.
Thanks to you all,
Best regards,
Petur
Or a slight mod of a multiplication trigger to the vlookup formula:Pete
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks