Based on the data in your sample sheet:
=VLOOKUP(B2,Budgets!A$1:B$275,2)
Hi,
Any Excel Guru's online
I have a excel speadsheet with 2 sheets.
On sheet 1 I have a list of usernames in column A and a list of codes in column B.
On sheet 2 I have the list of codes in column A and the description in column B
What formula do I need to enter on sheet 1 column C to lookup the table on sheet 2 find the code that matches and display the description cell in the formula cell?
I've attached the document with generic data, hopefully someone will be able to suss it out.
Thanks,
Michael
Based on the data in your sample sheet:
=VLOOKUP(B2,Budgets!A$1:B$275,2)
linkazoid (15th December 2011)
Try this...
You need to make sure your code column is numbers/text.
By adding the $ in front of the table_array will fix the table so when you copy/paste or drag down the list it will stay the same.
Hope it helps.
linkazoid (15th December 2011)
how did you get that to work as I am trying this in a different spreadsheet formatting the codes column as number with zero decimal points and using the exact vlookup forumla yet it does not work
Same for me, I cant replicate the formula on my sheet with the correct data.
I usually add FALSE on the end. Not sure what it does but it works.
EG:Code:=VLOOKUP(A1,B2:C4,2,FALSE)
I changed your column B (Code) to numbers by highlighting the column, right click Format Cells and in the Category list selected Number.
I then used the vlookup formula I included in the spreadsheet.
You use FALSE when you want vlookup to find only an exact match, TRUE finds an exact or approximate match.
hope that helps!
CAM (15th December 2011)
I just created a new spreadsheet and have got all cells formatted as general and added my own codes etc and it works fine there without formatting any columns as numbers etc
I attempted that on his original spreadsheet which I downloaded and it didn't make any difference, just kept erroring out giving me a hash symbol ie #
False - Exact match
True - Aproximate
Last edited by mac_shinobi; 15th December 2011 at 01:31 PM.
saved your excel file, closed excel down, right clicked on your excel file --> properties --> unblock --> apply
re opened your excel file and right clicked at the top right hand corner on both sheets --> clicked on the unlock checkbox until it was cleared --> apply
Then did the formula --> it still gave me #N/A in each of the cells, I found that if I double click in each of the code columns as though I was going to edit the number and click out of it, it fixed the vlookup issue
It is almost like each of your code values had spaces or extra characters attached to each of the codes because as soon as I clicked out of the code cell it found the relevant description
Very strange as I didn't have to do any of that... Are you using Excel 2010?
Yes I'm also using Excel 2010
Did an example dummy spreadsheet using a 4 digit number with one letter after each number ie 1234A and formatted the codes column as number with 0 decimals and it works fine.
I used the formula I posted in the original example spreadsheet and it worked first time!
There are currently 1 users browsing this thread. (0 members and 1 guests)