Multiple retruns from VLOOKUP

[kevinboo]

Hi All



I wonder can anyone help?

I am trying/wanting to use VLOOKUP to consolidate the information from 3 w/shts in the same w/book onto one. They all have one common Pt No in the left hand column which may or may not appear on this Master Sht.

I know how to use VLOOKUP to get info from 1 column, but when I try to drag the formula to the next column the cell ref to look up changes and the column number to look up from remains the same, I want the cell ref (or should I say the column letter) to remain the same and the row number to look at to increase by one.

Anyone ????? Please

Also when I try to auto fill my formula lose the sht to look up from ref?

Is there anyway I can :

A. Either fix the problems I have when dragging the formula

or

B. (Ideally) Enter 1 Formula in a cell which will look up and return the
values from say 14 or so Columns from the sheet I am asking the
VLOOKUP formula to get the info from

 

[Seti]

In answer to your first question, I think this should help. Say your basic formula in B2 is =VLOOKUP(A2,Sheet2!A2:J100,2,0), taking data from coulmn B on the other sheet. And you want =VLOOKUP(A2,Sheet2!A2:J100,3,0) in C2, etc. Instead try this:

=VLOOKUP($A2,Sheet2!$A$2:$J$100,COL(),0)

If I understand the problem correctly.

 

[macleanb]

Firstly familiarise yourself with absolute/relative adresses - these a good chapter in the help (just type "relative absolute" into the answer wizard and browse the articles. You will see that by using the $ symbol you can "fix" parts of your adressing.

As far as which column to look up, well I would use a header row to contain the offsets eg d3 could contain the offset "4" and your vlookup refer to D$4 (so that the row doesnt changebut the column does when you copy it)

 

[kevinboo]

Hi Seti & Mac

Many Thanks for your prompt responses , they were very helpfull, but I still have a problem.

I have overcome the 'Fixing' problem with my column reference using a combination of the advice you both gave, and so I can autofill down the column and all is Hunky Dory.

What is not happening is that when I drag the formula across to copy to the next columns the column ref to read from remains the same, it does not change as I want it to.

To Elaborate....My formula is in say col 'C' Row 2 and now reads :

C2 =VLOOKUP($A2,INVMAS,2,0)

When I autofill down it reads :

C3 =VLOOKUP($A3,INVMAS,2,0)
C4 =VLOOKUP($A4,INVMAS,2,0)
C5 =VLOOKUP($A5,INVMAS,2,0)

and so on thats great, just what I needed.

What it does not do whan I drag it across the next columns to copy is change the col ref to read from and so ALL the Col's in Row 2 ie D, E, F, G and so on read :

=VLOOKUP($A2,INVMAS,2,0)

When what I want is the Col ref to read from to increase automatically by 1 each time, and so read :

D2 =VLOOKUP($A2,INVMAS,3,0)
E2 =VLOOKUP($A2,INVMAS,4,0)
F2 =VLOOKUP($A2,INVMAS,5,0)
G2 =VLOOKUP($A2,INVMAS,6,0)

and so on...

Mac

You may be trying to tell me how to solve this problem with your ref to Offsets but im afraid you need to elaborate as I don't understand.....

Thanks again Guys ....

 

[fairwinds]

Hi,

If your first formula goes in e.g. C3, try:

=VLOOKUP($A2,INVMAS,COLUMN()-COLUMN($C$3)+2,0)

 

[kevinboo]

Fairwinds..

Many Thanks for your reply..

Unfortunately I cannot get your formula to work, I am sure it's not the formula but me....

Could you please type the formula as it would actually appear in my formula bar on my sheet, assuming the first formula goes in cell E2.


ALSO..... Seti, Mac (and yourslef Fairwinds)

I have found an alternative way of doing this using a solution from Mark.W utilizing an array formula.. See the Link Below.

Cheers Guys...

http://www.mrexcel.com/board2/viewtopic.php?p=89437#89437

 

[fairwinds]

If you put

=VLOOKUP($A2,INVMAS,COLUMN()-COLUMN($E$2)+2,0)

in E2, it should be the same as

=VLOOKUP($A2,INVMAS,2,0)

Then if you drag it to right it should increase one per column.

 

[kevinboo]

Fairwinds.

I see what you mean now You WERE showing it as it needs to be input in your original response ... I

have tried it and it Works... Many Thanks for your help I will now add this to my Excel Solutions Manual which is increasnig in size day by day.

Have A Good One !!! and thanks again...