+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
General Chat Thread, Excel Help Needed in General; Hi, Any Excel Guru's online I have a excel speadsheet with 2 sheets. On sheet 1 I have a list ...
  1. #1
    linkazoid's Avatar
    Join Date
    May 2007
    Location
    London
    Posts
    650
    Thank Post
    119
    Thanked 90 Times in 74 Posts
    Rep Power
    78

    Excel Help Needed

    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
    Attached Files Attached Files

  2. #2
    clareq's Avatar
    Join Date
    Dec 2005
    Location
    Doncaster
    Posts
    732
    Thank Post
    53
    Thanked 189 Times in 125 Posts
    Rep Power
    101
    Based on the data in your sample sheet:
    =VLOOKUP(B2,Budgets!A$1:B$275,2)

  3. Thanks to clareq from:

    linkazoid (15th December 2011)

  4. #3

    Join Date
    Sep 2005
    Location
    Lowestoft
    Posts
    49
    Thank Post
    8
    Thanked 7 Times in 7 Posts
    Rep Power
    20
    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.
    Attached Files Attached Files

  5. Thanks to tobyhacker from:

    linkazoid (15th December 2011)

  6. #4

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,828
    Thank Post
    3,340
    Thanked 1,057 Times in 978 Posts
    Rep Power
    365
    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

  7. #5
    linkazoid's Avatar
    Join Date
    May 2007
    Location
    London
    Posts
    650
    Thank Post
    119
    Thanked 90 Times in 74 Posts
    Rep Power
    78
    Same for me, I cant replicate the formula on my sheet with the correct data.

  8. #6
    CAM
    CAM is online now

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,201
    Thank Post
    842
    Thanked 377 Times in 292 Posts
    Blog Entries
    60
    Rep Power
    285
    I usually add FALSE on the end. Not sure what it does but it works.

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

  9. #7

    Join Date
    Sep 2005
    Location
    Lowestoft
    Posts
    49
    Thank Post
    8
    Thanked 7 Times in 7 Posts
    Rep Power
    20
    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!

  10. Thanks to tobyhacker from:

    CAM (15th December 2011)

  11. #8

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,828
    Thank Post
    3,340
    Thanked 1,057 Times in 978 Posts
    Rep Power
    365
    Quote Originally Posted by tobyhacker View Post
    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
    Last edited by mac_shinobi; 15th December 2011 at 01:31 PM.

  12. #9


    Join Date
    Feb 2007
    Location
    51.403651, -0.515458
    Posts
    9,079
    Thank Post
    232
    Thanked 2,724 Times in 2,009 Posts
    Rep Power
    798
    Quote Originally Posted by CAM View Post
    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.


  13. #10

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,828
    Thank Post
    3,340
    Thanked 1,057 Times in 978 Posts
    Rep Power
    365
    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

  14. #11

    Join Date
    Sep 2005
    Location
    Lowestoft
    Posts
    49
    Thank Post
    8
    Thanked 7 Times in 7 Posts
    Rep Power
    20
    Very strange as I didn't have to do any of that... Are you using Excel 2010?

  15. #12

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,828
    Thank Post
    3,340
    Thanked 1,057 Times in 978 Posts
    Rep Power
    365
    Quote Originally Posted by tobyhacker View Post
    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 ?
    Last edited by mac_shinobi; 15th December 2011 at 02:04 PM.

  16. #13
    linkazoid's Avatar
    Join Date
    May 2007
    Location
    London
    Posts
    650
    Thank Post
    119
    Thanked 90 Times in 74 Posts
    Rep Power
    78
    Yes I'm also using Excel 2010

  17. #14

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,828
    Thank Post
    3,340
    Thanked 1,057 Times in 978 Posts
    Rep Power
    365
    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.
    Attached Files Attached Files

  18. #15
    clareq's Avatar
    Join Date
    Dec 2005
    Location
    Doncaster
    Posts
    732
    Thank Post
    53
    Thanked 189 Times in 125 Posts
    Rep Power
    101
    I used the formula I posted in the original example spreadsheet and it worked first time!

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. more excel help needed please
    By RabbieBurns in forum Office Software
    Replies: 10
    Last Post: 6th September 2008, 11:53 PM
  2. help with excel macro needed
    By oyoung in forum Windows
    Replies: 0
    Last Post: 30th March 2007, 10:27 AM
  3. Help! Need a BIOS re-flashed
    By ajbritton in forum Hardware
    Replies: 2
    Last Post: 28th December 2006, 12:16 PM
  4. Help needed to reconfig my DC's
    By tosca925 in forum Windows
    Replies: 16
    Last Post: 20th November 2006, 10:45 PM
  5. Help needed creating a DMZ
    By pooley in forum Wireless Networks
    Replies: 12
    Last Post: 11th January 2006, 10:42 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •