VLOOKUP and OFFSET

[Budge]

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.
Please help
Thanks in advance.

Budge

 

[PaddyD]

Hi - welcome to the board.

If I understand what you want, this link might help you out:

http://www.mrexcel.com/board/viewtopic.php?topic=20135&forum=2

Post back with more if you need...

Paddy

 

[Aladin Akyurek]

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.
Please help
Thanks in advance.

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)

Note. Excel reads 0 as FALSE, so don't worry about that.

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

The latter requires the morefunc add-in, which you can download from

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

 

[Budge]

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

 

[Aladin Akyurek]

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

 

[PaddyD]

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
	A	B	C	D	E	F	G
1	Source
2	ID	One	Two	Three	Four	Five
3	A	5	3	8	6	1
4	B	1	3	5	4	8
5	C	2	8	7	2	1
6
7	Lookups
8		Two	Three	Five
9	A	3	8	1
10	B	3	5	8
11	C	8	7	1
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.

Paddy

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

 

[Budge]

[No message]

 

[Budge]

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
Please help

 

[Budge]

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
Please help

 

[PaddyD]

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


Paddy