Use Excel to return a single value based on a range?

[falseadvertise]

I need to pull a value from one sheet and put it on another based on a range of 1-40.

In column F I have F4:F43 (Total 40).
In Column A I have A4:A43 (Total 40).

What i need to do is return the value from A to a separate sheet based on the value in F. This is made difficult because currently column F is listed in order from 1-40. I will need to sort column F regularly and need the updates to reflect the changes.

Ex. - I am going to sort largest to smallest or smallest to largest and still need the same value in column A that is associated with the similar number in column F. So column F might say '1' (F4) and column A will say 'TOP 1' (A4) then i sort largest to smallest and F4 now says '40' and A4 now says 'TOP 40'. Whichever way i sort, i need my cell on the next sheet (assume sheet2!H1) to say the word 'TOP 1' and i need sheet!2H40 to say 'TOP 40'.

I can get an if statement to work if i do not sort the columns but when i do sort them it looses its consistency.

An equation that works before a sort:
=IF(Sheet1!F4=1,Sheet1!A4,"")

The equation i tried but wont work:
=IF(Sheet1!F4:F43=1,Sheet1!A4,"")

the problem is the Sheet1!F4:F43

Any ideas??

 

[falseadvertise]

Glenn,

I eventually figured out that the same formula would work (Busy day yesterday). I thought having the same values would just return any number that matched.

Thanks again for the help!