• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return Multiple columns from ONE Lookup Value

Yandeez

Yandeez

Member
I need to use the regular vlookup but return the values in columns 16, 17, 18. I thought to just copy the Vlookup formula and replace the column # argument with the column # I want but it doesnt work. Any ideas?
 
Hi ,

A VLOOKUP formula will not work if the lookup range ( the second parameter ) is not large enough to contain the column number ( the third parameter ).

Thus , if I enter a VLOOKUP formula as follows :

=VLOOKUP($K$1 , $A$2:$B$200 , 3 , False)

it will not work , since the lookup range is 2 columns whereas the third parameter is 3. If this has to work , I should change the formula to :

=VLOOKUP($K$1 , $A$2:$C$200 , 3 , False)

Narayan
 
I think the lookup value cell ref. that you are making you are keeping it as relative like A1, try making it absolute $A$1.

Regards,
 
Oh my it works. It seems you have to make the lookup value absolute. You just saved me from having to do 60 Vlookups one at a time. Thank you!!!!!
 
I need to use the regular vlookup but return the values in columns 16, 17, 18.
Click to expand...

If you're returning three columns related to one lookup term, note that there is a much more efficient way of doing this - particularly if you have hundreds or thousands of lookups that are returning data from very very large tables.

Here' some draft content from the book I'm working on - Excel for Superheroes and Evil Geniuses - that discusses this:

Let's say you want to look up a CustomerID from some Database Table, and return multiple columns associated with that record:
Multiple VLOOKUPs.gif

Now let’s look in turn at what each of those three VLOOKUPS is doing.

  • The first VLOOKUP spends most of its time scanning the ID column of the Lookup Table in order to find the row (if any) that contains a match, then grabs the corresponding value from the 2nd column of that row.
  • The second VLOOKUP spends most of its time scanning the ID column of the Lookup Table in order to find the row (if any) that contains a match, then grabs the corresponding value from the 3rd column of that row.
  • The third VLOOKUP spends most of its time scanning the ID column of the Lookup Table in order to find the row (if any) that contains a match, then grabs the corresponding value from the 4th column of that row.

Wait a minute: Those VLOOKUPS are stupid. In fact, I’m going to tell them they’re stupid:

You mean to tell me that you three VLOOKUPS each spent most of your effort looking up the address of the very same customer ID? Man, you guys are dumb! Why didn’t just one of you find out the relevant row, and pass that information on to the others? That would have saved two of you from doing time-consuming searches!

What we need is a non-selfish ‘scout’ function that goes off and finds exactly where something is in a lookup table, and then shares it's information with other functions. Let’s call this hypothetical function the “MATCH” function.

And then all we need is another type of lookup function – let’s call this hypothetical beast the “INDEX” function – that is happy to be told where to look for something. In fact, if we had three of these INDEX beasts, we could simply say to them:

  • You there: your job is to point at the FirstName column
  • And you: your job is to point at the LastName column
  • And lastly you. Yes, you. Your job is to point at the BirthDate column.
  • Now all of you listen up: MATCH will be returning from his scouting mission at any moment. He will tell you shortly exactly which row I want from the particular column you’re pointed at. So don’t muck around trying to work it out yourself, because I’m in a hurry.

Well, what do you know…Excel already has functions called MATCH and INDEX. Let’s see if they help us here.

Index Match_20141105.gif
 
Yandeez said:
Oh my it works. It seems you have to make the lookup value absolute. You just saved me from having to do 60 Vlookups one at a time. Thank you!!!!!
Click to expand...

Type of ref. of lookup value will depend upon how you are copying your formula (across / Down) and also whether there is one lookup value or multiple.

For e.g. If the lookup values are spread across rows say A1,A2,A3 and you want to copy VLOOKUP right and down than lookup value should be like $A1. This will not change the column ref. when you copy the formula across and will change the row ref. when you copy the formula down.

Where as if the lookup value is spread across column say A1,B1,C1 then the ref should be made like A$1, this will change column when you will copy the formula across but will not change the row when you will copy the formula down.

Do some experiment on this. You will get my point.

Regards,
 
You must log in or register to reply here.
Back
Top