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]

well, if you insist on playing hard to get...


Given that the = operator is commutative (if a = b and b = c then a = c), you can use the following:

=AND(B1=B2,B1=B3,B1=B4)

see the example:

Book4.xls
	A	B	C	D	E	F
1		1	1	1	1	0
2		1	1	1	0	0
3		1	1	0	1	0
4		1	0	0	1	0
5	Allequal?	TRUE	FALSE	FALSE	FALSE	TRUE
6
7
Sheet3

paddy

 

[Budge]

Sorry Paddy,for not replying sooner but I was still experimenting with your solution.As I haven't used arrays before,I am having problems with entering the formula.Ctrl,Shift and Enter I understand to give the braces but how do you go into the formula to edit it i.e. to make normal brackets for "A9".When I try to edit the formula I get the error message"The formula you are trying to enter.....contains an error"
So it is basically a problem of editing a formula so the braces are in the right spot.
For your quick reference the formula you sent me is
{=VLOOKUP(A9,ATable,{3,4,6},0)}
My lookup range is called "Look" and for this exercise only it refers to range "A6:I21" and I want the values returned that correspond to A9 from Col's C,D and F.
I wanted to exhaust every avenue before I came back to you and made my ignorance public information.It appears that every avenue has now become a Cul de Sac and I need direction.
Thanks Paddy..
Sincerely,
Budge

 

[PaddyD]

For your quick reference the formula you sent me is
{=VLOOKUP(A9,ATable,{3,4,6},0)}
My lookup range is called "Look" and for this exercise only it refers to range "A6:I21" and I want the values returned that correspond to A9 from Col's C,D and F.

try:

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

array entered into 3 cells at once (i.e. select 3 cells, go to the formula bar, type in the formula + press control+shift+enter)

you don't include the curly brackets in the formula - excel will add it when you press control+shift+enter

Paddy

 

[Budge]

After this posting I'm going underground for awhile.This is plain embarassing.I still can't get this Ctrl+Shift+Enter.
I've selected three cells with the mouse.I go to the formula bar and type =vlookup(A9,Look,(2,3,6),0)and then Ctrl,Shift,Enter simultaneously.Error.I then position the cursor at the beginning of the formula and hit Ctrl,Shift,Enter.Error.I then remove the brackets surrounding 2,3,6 and again error.
Tried every combination.
Apologies for the regularity of this monotony.
Sincerely,
Budge
PS Thanks heaps for the return message on the AND function for evaluating multi cells.

 

[PaddyD]

If you are typing:

=vlookup(A9,Look,(2,3,6),0)

you are using the wrong brackets. It needs to be

=vlookup(A9,Look,{2,3,6},0)

(i.e. the brackets round 2,3,6 are 'curly' brackets)

Paddy

 

[Budge]

Hey Paddy,
I thought that Excel put the the curls in after you hit ctrl,shift,enter.So is it true that you type the curls around the relevant columns and then hit ctrl,shift,enter which then puts the array brackets at the begining and end of the formula.
Anyway,I selected 3 cells with the mouse,typed the formula into the formula bar putting the braces around 2,3,6 manually.Then I did Ctrl,shift,Enter to put the braces around the beginning and end of formula. It only returned the first column reference i.e. 2 but not 3 and 6.Typed the formula in again without the braces at the beginning and end of formula,put manual braces around {2,3,6} and got only column 2 returned again.
Your patience is astonishing!!

Sincerely,
Budge

 

[PaddyD]

Budge,

sounds like you are doing everything right now, so I'm suprised you're not getting results.

You can send me the workbook if you want - check your private messages. (the link is in the top right of the screen)

Paddy

 

[Juan Pablo González]

you should type in the formula bar

=vlookup(A9,Look,{2,3,6},0)

and then, press Control Shift Enter. if you'er using an Excel version different than English, the commas inside the { } probably change. For example, in my spanish version I would tyep

=BUSCARV(A9,Look,{2;3;6},0)

 

[Budge]

Paddy,
Just in case you didn't get my email,everthing is ok now.Thanks for everything.You've been a great help
Best Regards
Budge

 

[PaddyD]

didn't get the file, but glad you've got it working
Paddy