problem with using an Array formula for VLOOKUP

[omid020]

Hey,

I have used this array in attached file ( Sheet1 > N3 ) to lookup station number but it returns #N/A :

Please Login or Register  to view this content.

File: Test_Array

I am confused why it does not work! Could you please guide me?

[FDibbins]

1st, you have some circular reference errors in that file that you need to take care of...
Sheet1 N3
Trim3 D273:E273 (you have the SUM formula within it's sum range)
Same with 221, 301
Final D72, same problem with SUM


Once you have fixed those...
where are you finding that station number from - I cannot make sense of your formula?

[omid020]

You are right! I have modified those unusual sums but still the formula does not work and get circular reference alert !

I want to lookup FOP No. from all available sheets and return correspond Station No. into column M of Sheet1; I think the final formula should be like this :

Please Login or Register  to view this content.

Please kindly check new attachment.

[omid020]

Has anybody another idea on this issue?