# VLOOKUP and OFFSET

#### Budge

##### Board Regular
I want to be able to populate 6 cells of data returned from 1 Vlookup using an exact match with a unique lookup value.I don't want to enter a lookup formula in each cell if it can be avoided.
To illustrate,say in Sheet1 I have data in a range from A1 to Z10000 which is given a range name "Budge".The lookup values are contained in column A. In sheet 2,I need the values from A1 returned in columns B,C,D,E,F,G.
So in sheet 2 cell ref B1 the formula will be =vlookup(a1,Budge,2,false) to return the value of Column B in sheet1.I don't want to copy this formula into each of the cells changing the column reference each time.
I don't think Index and Match are needed as i am only refering to a single criteria,ie unique values in column A.I was hoping that Offset might help but alas have been failing.Serious user error!!
In the time taken writing this and searching for a solution I could have done it via multi vlookups and climbed to Base camp at Everest,but that's not the point..it's the challenge....a little like going for the hottest babe in the latest chic nightclub...sorry,too much time spent staring into this screen!!
One more thing,evaluating 3 cells for comparison.And/Or Boolean functions cater for 2 scenarios and so A1=B1=C1 clearly will not work.Concatenating is not an option here.

Budge

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
On 2002-08-27 14:44, Budge wrote:
I want to be able to populate 6 cells of data returned from 1 Vlookup using an exact match with a unique lookup value.I don't want to enter a lookup formula in each cell if it can be avoided.
To illustrate,say in Sheet1 I have data in a range from A1 to Z10000 which is given a range name "Budge".The lookup values are contained in column A. In sheet 2,I need the values from A1 returned in columns B,C,D,E,F,G.
So in sheet 2 cell ref B1 the formula will be =vlookup(a1,Budge,2,false) to return the value of Column B in sheet1.I don't want to copy this formula into each of the cells changing the column reference each time.
I don't think Index and Match are needed as i am only refering to a single criteria,ie unique values in column A.I was hoping that Offset might help but alas have been failing.Serious user error!!
In the time taken writing this and searching for a solution I could have done it via multi vlookups and climbed to Base camp at Everest,but that's not the point..it's the challenge....a little like going for the hottest babe in the latest chic nightclub...sorry,too much time spent staring into this screen!!
One more thing,evaluating 3 cells for comparison.And/Or Boolean functions cater for 2 scenarios and so A1=B1=C1 clearly will not work.Concatenating is not an option here.

Budge

I suppose you want to copy across from B to G. The trouble is how to increase the index for the column to look in while copying.

Insert a row in Sheet2.
In B1 enter 1 and in C1 2. Select B1:C1 and copy across to G1.

In B2 enter and copy across to G2 then down:

=VLOOKUP(\$A2,Budge,B\$1,0)

You might get #N/A's. Either keep them or avoid them using one of:

=IF(ISNUMBER(MATCH(\$A2,INDEX(Budge,0,1),0)),VLOOKUP(\$A2,Budge,B\$1,0),"")

=IF(ISNA(SETV(VLOOKUP(\$A2,Budge,B\$1,0))),"",GETV())

http://longre.free.fr/english/index.html

Thanks guys.Very fast response.I don't really "get it".Never mind,I will stick to using a lookup in each cell changing the column reference each time.
BTW How do I compare 3 cells say a1=b1=c1 as per my first question??

Sincerely,
Budge
This message was edited by Budge on 2002-08-27 15:35

...
I don't really "get it".Never mind,I will stick to using a lookup in each cell changing the column reference each time.

What is not clear w.r.t. copying the VLOOKUP formula?

BTW How do I compare 3 cells say a1=b1=c1 as per my first question??

Care to elaborate what you mean by comparing 3 cells?

On 2002-08-27 15:27, Budge wrote:
Thanks guys.Very fast response.I don't really "get it".Never mind,I will stick to using a lookup in each cell changing the column reference each time.

see if this works for you:

Problem

A lookup value has many columns of data associated with it. Write the shortest vlookup formula that will return a subset of those columns.

See the simulation below:
Book5
ABCDEFG
1Source
2IDOneTwoThreeFourFive
3A53861
4B13548
5C28721
6
7Lookups
8TwoThreeFive
9A381
10B358
11C871
12
13
Sheet3

Explanation:

It is possible to array enter a vlookup formula.

{=VLOOKUP(A9,ATable,{3,4,6},0)}

in this case, we want 3 columns of data returned. So, we work out which columns they are in our table array (cols 3,4 & 6 in this case), then we select 3 cells to put our results in, write the vlookup formula with the multiple column lookup {3,4,6} & array enter the formula into all three cells at once. Voila - three columns returned, one formula entered.

Return
This message was edited by PaddyD on 2002-08-27 16:02

[No message]

I want to evaluate 4 cells,say A1,A2,A3,A4.
If A1 = A2= A3=A4, then do something.The problem is if A1=A2 but A3 does not = A1,excel passes the test as true as only the first 2 arguments are evaluated.If have tried the "And" function but user error again

I want to evaluate 4 cells,say A1,A2,A3,A4.
If A1 = A2= A3=A4, then do something.The problem is if A1=A2 but A3 does not = A1,excel passes the test as true as only the first 2 arguments are evaluated.If have tried the "And" function but user error again

I'll give you a hand with this if you tell me whether my earlier post helped with your other problem!

Replies
5
Views
203
Replies
1
Views
324
Replies
4
Views
462
Replies
2
Views
1K
Replies
1
Views
1K

1,221,523
Messages
6,160,321
Members
451,637
Latest member
hvp2262

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

### Which adblocker are you using?

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

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