Close

Results 1 to 4 of 4
  1. #1
    DF Jedi satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,653
    Thanks
    154
    Thanked:        559
    Karma Level
    416

    Default Another Excel problem

    I'm sure this is an 'easy to fix' problem but I'm being a thicko.

    I've attached a spreadsheet with six columns.
    What I want is for it to take colmn D and match it with column A. Then if it finds a match put the corresponding line in column B and C into columns E and F.
    For example:
    GD10ELECMOD (at D4) is located at (A5) so it populates T1000 ELECTRICAL HARDWARE and MOD in columns E and F so it will sort all of column D out without me physically looking and copying and pasting.
    Hope you can understand what I'm getting at?
    Attached Files Attached Files
    Just use enough water to cover your vegetables,the same goes for when you're having a bath....

  2. #2
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,848
    Thanks
    1,034
    Thanked:        783
    Karma Level
    302

    Default Re: Another Excel problem

    For e column (start at E2 and drag down)
    =VLOOKUP(D2,$A$2:$C$7046,2,0)

    For column F (start at E2 and drag down)
    =VLOOKUP(D2,$A$2:$C$7046,3,0)

    Vlookup({What to search for}, {where to search 1st column of a grid}, {which column to return from grid}, {Exact Match (False)}

    So for cell E2 we are looking up D2 in the grid A2:C7036 (the $$ are to fix the grid from changing when dragging) and we want to return 2nod column and what an exact match.

    This will return only the first match so it is better if column A is unique.

    Thanks to akimba

    satzzz (27th June 2018) 


  3. #3
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,848
    Thanks
    1,034
    Thanked:        783
    Karma Level
    302

    Default Re: Another Excel problem

    Also if you add rows to the A-C columns you will have to extend the grid in the formula so might be wise to preempt that by changing to
    =VLOOKUP(D2,$A$2:$C$20000,2,0)

    or you can do =VLOOKUP(D2,A:C,2,0) I just being old skool think that looking up over a million rows just because being lazy each time is a bit too drastic.

    As further down the line this will start going funky and no one will work it out ;-)

    Thanks to akimba

    satzzz (27th June 2018) 


  4. #4
    DF Jedi satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,653
    Thanks
    154
    Thanked:        559
    Karma Level
    416

    Default Re: Another Excel problem

    Cheers pal
    Just use enough water to cover your vegetables,the same goes for when you're having a bath....

Social Networking Bookmarks

Posting Permissions

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