Excel Help Needed

[linkazoid]

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

Client Codes.xlsx

[clareq]

Based on the data in your sample sheet:

=VLOOKUP(B2,Budgets!A$1:B$275,2)

[tobyhacker]

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.

Client Codes.xlsx

[mac_shinobi]

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

[linkazoid]

Same for me, I cant replicate the formula on my sheet with the correct data.

[CAM]

I usually add FALSE on the end. Not sure what it does but it works.

 

EG:

=VLOOKUP(A1,B2:C4,2,FALSE)

[tobyhacker]

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!

[mac_shinobi]

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!

 

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

Edited December 15, 2011 by mac_shinobi

[Arthur]

I usually add FALSE on the end. Not sure what it does but it works.

The TRUE/FALSE bit at the end, is to tell Excel whether the list is (or isn't) sorted.

 

http://i.imgur.com/GId2a.png

[mac_shinobi]

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

[tobyhacker]

Very strange as I didn't have to do any of that... Are you using Excel 2010?

[mac_shinobi]

Very strange as I didn't have to do any of that... Are you using Excel 2010?

 

Yes I am using Excel 2010 not sure about the OP ( linkazoid )

 

Is that a known fault with Excel 2010 or what exactly ?

Edited December 15, 2011 by mac_shinobi

[linkazoid]

Yes I'm also using Excel 2010

[mac_shinobi]

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.

VLOOKUP Example.xlsx

[clareq]

I used the formula I posted in the original example spreadsheet and it worked first time!

[linkazoid]

Thank You,

 

I had to copy all my data into a new spreadsheet without any formatting and it's now working ok!

 

Michael

[LosOjos]

The TRUE/FALSE bit at the end, is to tell Excel whether the list is (or isn't) sorted.

 

Sorry but that's wrong... it tells Excel whether you're looking for an exact match or a close match, doesn't matter whether the list is sorted. [EDIT] As @pcstru quite rightly points out, the results you get from a range lookup will vary depending on how the list is sorted. To clarify: the range_lookup boolean itself is nothing to do with whether a list is sorted or not, but your mileage may vary depending on how said list has been sorted. [/EDIT]

 

You can try it yourself; make a list of random words but make one of them "rage" for example. Make sure none of them say "range". Now do a VLOOKUP for "range" and watch what happens when you change between true and false - true enables a range lookup and will return "rage" as the closest match, false looks for an exact match and so throws an error.

Edited December 15, 2011 by LosOjos

[pcstru]

Sorry but that's wrong... it tells Excel whether you're looking for an exact match or a close match, doesn't matter whether the list is sorted.

 

If you are looking for an approximate match, then you may get unexpected results if the list isn't sorted into ascending order. So it does matter.

[LosOjos]

If you are looking for an approximate match, then you may get unexpected results if the list isn't sorted into ascending order. So it does matter.

 

Fair point, what I meant though was that the option was nothing to do with whether or not the list is sorted as was suggested (although yes, you're right, results will vary depending on whether it is sorted or not)

[Arthur]

Sorry but that's wrong... it tells Excel whether you're looking for an exact match or a close match

Well spotted! It's strange that Mr Chandoo (a Microsoft Excel MVP) would make a mistake like this.

[LosOjos]

Well spotted! It's strange that Mr Chandoo (a Microsoft Excel MVP) would make a mistake like this.

 

Yeah it wasn't a pop at you just had to point it out in case anybody reading is sat there working themselves in to a fury because it wasn't working as expected