Puzzled by a VLOOKUP formula

[Steviefiege]

Hi,

I have this formula: =IFERROR(VLOOKUP($B3,'Main Squad'!$A:$AM,MATCH(INDEX(Sheet1!C:C,ROW()),'Main Squad'!$A$17:$AM$17,0),0),"") which works absolutely perfectly.

I then created a new page & using the same formula but tweaked a bit changed it to this:
=IFERROR(VLOOKUP($B19,Scouting!$A:$AL,MATCH(INDEX(Sheet4!C:C,ROW()),Scouting!$A$17:$AM$17,0),0),"")

I set Sheet4 up exactly the same as Sheet1 but it doesn't work. Sheet 4 is working with the formula's but for some reason it's not having it. Any ideas?

Rgs
Steve

 

[steve the fish]

Its a weird set up you have there. So you have a series of 'headers' in column C of sheet 4 that are transposed in row 17 of scouting?

 

[Steviefiege]

Yeah, exactly that. The first formula for sheet1 works perfectly for my main squad. But i wanted to do the same again for the players i've scouted so i set it all up again, exact same as the first formula but obviously changed the worksheet names that there on & the corresponding cells. Sheet4 works exactly the same as sheet1 but looks off my scouting page not my main squad page but for some reason the results are just blank (thats because of the IFERROR i know that). If the IFERROR wasnt in it just comes up #N/A.

 

[steve the fish]

If it works in one sheet it will work in all. Can we try pinpoint the error. In the formula that produces the error go to the formula bar and click in the MATCH (say between the A and the T. Press the fx button. On the resultant dialog box do you see a number being produced or the result blank or an error?

 

[Steviefiege]

Here's what it said:

Lookup_value: INDEX(Sheet4!C:C,ROW()) = ""
Lookup_array: Scouting!$A$17:$AL$17 = {"Player Name","Total Rating","Cor","....
Match_type: 0 = 0

Then at the bottom it says Formula result =

 

[steve the fish]

Ok what row is this formula in because essentially that tells me there is nothing in the Sheet4 column C at that row number so you have no lookup value.

 

[Steviefiege]

In sheet4 its starts in c3

 

[steve the fish]

Which row is the formula in?

INDEX(Sheet4!C:C,ROW())

This will produce a different value dependant on which row its placed in.

 

[Steviefiege]

I've sorted it!!!!! The formula was in the wrong row compared to the other one. Thank you soo much for your help, your last reply triggered it off.