Multiple retruns from VLOOKUP

kevinboo

Board Regular
Joined
Nov 8, 2004
Messages
146
Hi All

:rolleyes:

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 :cry:

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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
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

Well-known Member
Joined
Dec 10, 2004
Messages
715
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

Board Regular
Joined
Nov 8, 2004
Messages
146
Hi Seti & Mac

Many Thanks for your prompt responses :biggrin: , 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..... :oops:

Thanks again Guys .... (y)
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Hi,

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

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

kevinboo

Board Regular
Joined
Nov 8, 2004
Messages
146
Fairwinds..

Many Thanks for your reply.. :p

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

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... (y)

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

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Board Regular
Joined
Nov 8, 2004
Messages
146
Fairwinds.

I see what you mean now :oops: You WERE showing it as it needs to be input in your original response :LOL: ... 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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,968
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top