Cell reference does not change in nested vlookup when I sort

Question

I am creating a worksheet "Workbook" that is referencing another tab in the same book "Previous Day". The formula I created does a VLOOKUP to the other tab, and if it finds an exact matching value, it uses the data from Previous Day. If it does not find the value, it uses a manually entered value in "Workbook".

=IFERROR(IF(VLOOKUP($A2,'EDIOutput_Previous Working Day'!A:AI,24,FALSE)<>0,VLOOKUP(Workbook!$A2,'EDIOutput_Previous Working Day'!A:AI,24,FALSE),I2),I2)

Everything works great, unless you sort "Workbook". When I do, the cell reference in the first VLOOKUP changes with the new location of the row, but the second one does not:

=IFERROR(IF(VLOOKUP($A2,'EDIOutput_Previous Working Day'!A:AI,24,FALSE)<>0,VLOOKUP(Workbook!$A204,'EDIOutput_Previous Working Day'!A:AI,24,FALSE),I2),I2)

Can anyone help me figure out how to make both references change, or if I'm just barking up the wrong tree with this formula?

Answer 1

I see the same behaviour and I don't have an explanation for it.

Why do you reference the worksheet name in the second Vlookup, though? If you just use the cell reference without the sheet name, the formula will work fine and it will survive the sort.