# Vllokup Break down

#### gazza uk

##### New member
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
When I try and do this in vlookup there is no \$ at all nor the word MATCH

#### bgoree09

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

#### gazza uk

##### New member
vlookup

so your formula looked like ??

#### Bob Phillips

##### Super Moderator
Staff member
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
Is that thread related to this question, if so, how? Is this one answered?

#### gazza uk

##### New member
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
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
=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
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
I think I understand it all now thanks for your help how do I mark it as problem solved

#### gazza uk

##### New member
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
The highlighted 0 means do an exact match.

#### gazza uk

##### New member
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

Where am I going wrong

#### gazza uk

##### New member
all sorted now thanks for all the help