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!

 

[Groovicles]

Yes, the ones with the wrong results appear to all have spaces in them. That should be it. Thanks very much for your help etaf.

 

[etaf]

your welcome

 

[Groovicles]

Hi etaf,

I've got an extended question. I'm wondering, how could i extend this formula to cover a total of 5 columns instead of 2 (64-68). I couldn't be that I'd just write them one after another in a nested if statement, would it?

 

[etaf]

dont understand

your not using 2 columns , your using $F$3:$CG$6472 F to CG hence the need for 64 and 65

the lookup uses the column you specify first in your range so in this case F

if you only wanted 2 columns
then F:G
and you can use the numbers , 1 for F and 2 for G

 

[Aladin Akyurek]

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!

Assumption: VLOOKUP, if successful, returns text (the length of which > 0).

=LOOKUP(REPT("z",255),VLOOKUP(A9,'c:xyz'!$F$3:$CG$6472,{64,65},0))

 

[Groovicles]

Hi etaf,

Sorry. So the vlookup is searching columns 64 and 65. If 64 is blank, then 65 is used. What I'm asking if that can be extend to 68. So if columns 64 and 65 are blank, then 66 is used. If those three are blank, then 67, etc.

 

[Aladin Akyurek]

Hi etaf,

Sorry. So the vlookup is searching columns 64 and 65. If 64 is blank, then 65 is used. What I'm asking if that can be extend to 68. So if columns 64 and 65 are blank, then 66 is used. If those three are blank, then 67, etc.

Have also a look at post #15.

 

[Groovicles]

Hi Aladin,

Thank you for your reply. I must say i only understand about half of the formula you suggested. What does =LOOKUP(REPT("z",255) mean? Also, I assume {64,65} could be expanded to {64,65,66,67,68} if needed, correct?

Thanks!

 

[Aladin Akyurek]

Hi Aladin,

Thank you for your reply. I must say i only understand about half of the formula you suggested. What does =LOOKUP(REPT("z",255) mean?

REPT("z",255) is the lexically 'biggest" string which is improbable to occur in references of interest to us. When used as the look up value in LOOKUP (and kindred functions), we get the last text value in the reference with which the function is fed.

Also, I assume {64,65} could be expanded to {64,65,66,67,68} if needed, correct?

Yes, exactly.

Thanks!

You are welcome.

 

[Groovicles]

hi Aladin,

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