Vllokup Break down

G

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)
 
When I try and do this in vlookup there is no $ at all nor the word MATCH
 
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,
 
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.
 
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).
 
Is that thread related to this question, if so, how? Is this one answered?
 
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
 
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.
 
=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
 
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)
 
I think I understand it all now thanks for your help how do I mark it as problem solved
 
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
 
The highlighted 0 means do an exact match.
 
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
 
You must log in or register to reply here.
Back
Top