Make formula look in a different column if a 0/blank is returned

[adx1000]

Hi all,

I am trying to figure out how to modify a formula. At the moment i have

Please Login or Register  to view this content.

Which is looking in column 8 in a seperate sheet in my workbook. on occasion there is no data in column 8, and as such the formula returns a 0.
Is there any way of getting the formula to search in column 9 if a 0 is returned.

Hope this is clear.

Thanks in advance!

[amy_d2]

HI Adx,

i think this would do

=IF(VLOOKUP(A3,INDIRECT(F3),8,FALSE)=0,VLOOKUP(A3,INDIRECT(F3),9,FALSE),VLOOKUP(A3,INDIRECT(F3),8,FALSE))

[judgeh59]

try this

Please Login or Register  to view this content.

untested

[adx1000]

This looks like its working, thank you so much Amy!

HI Adx,

i think this would do

=IF(VLOOKUP(A3,INDIRECT(F3),8,FALSE)=0,VLOOKUP(A3,INDIRECT(F3),9,FALSE),VLOOKUP(A3,INDIRECT(F3),8,FALSE))

[Tony Valko]

Here's another one:

=VLOOKUP(A3,INDIRECT(F3),8+(VLOOKUP(A3,INDIRECT(F3),2,0)=0),0)