Vlookup formula not changing properly

[Steviefiege]

Hi,
I have 14 columns with Vlookup formula's in them, here's one for example:

=IFERROR(VLOOKUP($B4,'Main Squad'!$A:$AM,MATCH(Sheet1!C4,'Main Squad'!$A$17:$AM$17,0),0),"")

They all work fine, until i use the sort button in the table there in. When i click on the persons name to put it in alphabetical order, where i've put the formula in bold it goes haywire. Instead of goin down c4, c5, c6 etc... like i want it too it keeps the original number in so it could say for example c4, c5, c57, c6, etc. How do i stop it from doing that?

Rgs
Steve

 

[Peter_SSs]

What is the name of the sheet that this formula is on?

 

[Steviefiege]

The name of the sheet that the formula is on is called 'Player Position Availability'. In the formula, the Sheet1!C4 is actually just called Sheet1, its just my working out page.

 

[Peter_SSs]

The name of the sheet that the formula is on is called 'Player Position Availability'. In the formula, the Sheet1!C4 is actually just called Sheet1, its just my working out page.

I didn't understand that.

You quoted a formula
=IFERROR(VLOOKUP($B4,'Main Squad'!$A:$AM,MATCH(Sheet1!C4,'Main Squad'!$A$17:$AM$17,0),0),"")

Is that exact formula on 'Sheet1' or is it on 'Player Position Availability'?


If the above formula is not the exact formula you are using that you are having the sorting problem with, what is the exact formula and what sheet is it on?

 

[Steviefiege]

That exact formula is on 'Player Position Availability'

The formula in Sheet1 is this:
=IF(VLOOKUP('Player Position Availability'!$Q3,Positions!$A:$O,COLUMNS($A$3:A3)+1,0)="","",VLOOKUP('Player Position Availability'!$Q3,Positions!$A:$O,COLUMNS($A$3:A3)+1,0))

 

[RoryA]

References to other worksheets will move as the cells are sorted. If you don't want that, you'll need to use something like an INDEX formula. Assuming a formula in row 4, you'd use something like:

=IFERROR(VLOOKUP($B4,'Main Squad'!$A:$AM,MATCH(INDEX(Sheet1!C:C,ROW()),'Main Squad'!$A$17:$AM$17,0),0),"")

IMO, this is a weird layout.

 

[Steviefiege]

Thank you Rory, that's done it