If vlookup is blank in one column, then vlookup in another column

[Groovicles]

Hi everyone,

I have the following formula:

=IF(ISNA(VLOOKUP(A9,'c:xyz'!$F$3:$CG$6472,64,FALSE)),"",(VLOOKUP(A9,'c:xyz'!$F$3:$CG$6472,65,FALSE)))

So what I'm trying to do, is that vlookup to column 64 is blank, then do a vlookup on column 65. The formula is not working. Its only reading one column, but if its blank its not reading the next, its only showing a blank result. Any ideas?

Thanks everyone!

 

[Aladin Akyurek]

hi Aladin,

Your suggestions works perfectly. Thank you very much for your help and your explanation. I was not aware of the REPT function.

You are welcome. Thanks for providing feedback.

 

[Groovicles]

Hi Aladin (or anyone else who'd like to help)

I've found an odd occurance with your formula. For some reason, its not picking up on results in row 64. I've tried to reverse the order of the rows, putting 68 first and then counting back to 64, but then results for row 68 don't show. Any idea why or how I could correct this?

Thanks,

 

[Aladin Akyurek]

Hi Aladin (or anyone else who'd like to help)

I've found an odd occurance with your formula. For some reason, its not picking up on results in row 64. I've tried to reverse the order of the rows, putting 68 first and then counting back to 64, but then results for row 68 don't show. Any idea why or how I could correct this?

Thanks,

Care to post the formula as you implemented it?

 

[Groovicles]

hi Aladin,

Here you go:

=IF(A19="","",LOOKUP(REPT("z",255),VLOOKUP(A19,'c:\xyz'!$F$3:$CG$6472,{64,65,66,67,68},0)))

 

[Aladin Akyurek]

hi Aladin,

Here you go:

=IF(A19="","",LOOKUP(REPT("z",255),VLOOKUP(A19,'c:\xyz'!$F$3:$CG$6472,{64,65,66,67,68},0)))

Looks ok.

The formula picks out the last text value which obtains from columns 64, 65, 66, 67, and 68. Suppose we have:

{"Jad",0,"Kad","Vad","Qad"}

we'll get Qad.

If you would rather want Jad, let's reverse the column numbers...

=IF(A19="","",LOOKUP(REPT("z",255),VLOOKUP(A19,'c:\xyz'!$F$3:$CG$6472,{68,67,66,65,64},0)))

Does this help?

 

[Groovicles]

hi Aladin,

Yes I figured that's what your code did. I'm wondering if its just a problem with the source excel document or our network rather than the code, since it seems to work perfectly on some instances and in others it does not. Thanks very much!

 

[Aladin Akyurek]

hi Aladin,

Yes I figured that's what your code did. I'm wondering if its just a problem with the source excel document or our network rather than the code, since it seems to work perfectly on some instances and in others it does not. Thanks very much!

What is the full path to the table?