Close

Results 1 to 4 of 4
  1. #1
    DF VIP Member
    satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,662
    Thanks
    157
    Thanked:        564
    Karma Level
    469

    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 VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    368

    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 VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    368

    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 VIP Member
    satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,662
    Thanks
    157
    Thanked:        564
    Karma Level
    469

    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....

Similar Threads

  1. [HELP] Excel problem with (i'm sure) an easy fix.
    By satzzz in forum Programming
    Replies: 4
    Last Post: 29th July 2016, 01:09 PM
  2. [HELP] Another Excel Formula problem...
    By DJ OD in forum PC Software
    Replies: 3
    Last Post: 9th March 2010, 02:00 PM
  3. [HELP] Excel Formula problem
    By DejaVu in forum PC Software
    Replies: 5
    Last Post: 10th May 2009, 07:06 PM
  4. [HELP] Excel problem
    By Argyll in forum PC Software
    Replies: 3
    Last Post: 3rd May 2009, 07:05 PM
  5. Excel Problem
    By MonkeyBalls in forum PC Problems
    Replies: 3
    Last Post: 10th February 2006, 04:23 PM

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
  •