Vllokup Break down

gazza uk

New member
Joined
May 29, 2014
Messages
41
Reaction score
0
Points
0
Can someone please break this line down into what does what please =VLOOKUP($A4,'Division1&2 season 42'!$B:$AF,MATCH(B1,'Division 1&2 season42'!$B$1:$AF$1,0),0)
 

gazza uk

New member
Joined
May 29, 2014
Messages
41
Reaction score
0
Points
0
When I try and do this in vlookup there is no $ at all nor the word MATCH
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good afternoon,

It looks like the match is being used in the Column Reference portion of the Vlookup formula. So, it is finding A4, in the range Division1&2 season 42 B:AF and returning the column returned by the match formula which is finding b1 in the top row of the same sheet. It might be cleaner if you used an index-match combination. You could use:

= Index( Division1... B:AF, match( A4, Div... B:AF, 0), match( B1, Div... B1:AF, 0))

This will do the same thing, but it's a bit more straightforward. It makes kind of a matrix out of the table and then finds the matching row and column and returns the result.

Hopefully this is easy enough to understand. Best of luck,
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Your formula didn't work for me, couldn't see why, so I replaced the range in the MATCH formula by selecting B1:AF1 on the other sheet, made it absolute, and lo and behold, it worked fine.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Oh I see what it is now. The sheet name in the MATCH formula has an extraneous space between the Division and 1, there should be none (or the other one is missing a space).
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Is that thread related to this question, if so, how? Is this one answered?
 

gazza uk

New member
Joined
May 29, 2014
Messages
41
Reaction score
0
Points
0
Yes it is related but only in trying to understand the vlookup part and no its not solved as yet still looking for a break down
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
As I said, the correction I posted worked for me. Whether it works correctly only you know, but you aren't saying, but otherwise I am out of ideas.
 

gazza uk

New member
Joined
May 29, 2014
Messages
41
Reaction score
0
Points
0
=VLOOKUP($A4,'Division 1&2 season 42'!$B:$AF,MATCH(B1,'Division1&2 season 42'!$B$1:$AF$1,0),0)
Lookup value=$A4
Table _ array ='Division 1&2 season 42'!$B:$AF
Col_index_num=MATCH(B1,'Division 1&2 season 42'!$B$1:$AF$1,0)
Ok this is what I mean
I understand the lookup value now it’s the text you want tolook up
I understand the table array is the area I want it to searchfor the text from the lookup value
What I am not understanding is the col index num (the highlightedpart
Am I right in saying its telling it to look for the text inB1 and to find a match in B1 to AF 1 on the said sheet therefor the answer to the whole problem is is lookup the rowcontaining $a4 then look up the column containing B1 and bring back the answer
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Just look at that formula

=VLOOKUP($A4,'Division 1&2 season 42'!$B:$AF,MATCH(B1,'Division1&2 season 42'!$B$1:$AF$1,0),0)
 

gazza uk

New member
Joined
May 29, 2014
Messages
41
Reaction score
0
Points
0
I think I understand it all now thanks for your help how do I mark it as problem solved
 

gazza uk

New member
Joined
May 29, 2014
Messages
41
Reaction score
0
Points
0
Ok Bob I am still not 100% sure what the last bit is I get b$s1:af$1 but what does the highlighted 0 stand for I know the very last 0 is the range lookup VLOOKUP($A4,'Division 1&2 season 42'!$B:$AF,MATCH(B1,'Division1&2 season 42'!$B$1:$AF$1,0),0)
I know the last 0 is the range lookup
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
The highlighted 0 means do an exact match.
 

gazza uk

New member
Joined
May 29, 2014
Messages
41
Reaction score
0
Points
0
Still can’t see this I am trying to enter the formula intothe activity sheet in cell Z8 to get the data I have entered in the C.T.T.Hsheet (Bazzerion) as a lookup
This is what I am putting in
clip_image001.png


Where am I going wrong
 
Top