V look up help please - ignore blank cells

[jedibrown]

Hi,

My current formula is:

=VLOOKUP($G$3:$G$6000,$Q$3:$R$14,2,FALSE)

Please could somebody tell me how I get it to ignore blanks and not come up with #N/A?

Thanks

 

[Special-K99]

The first parameter in your VLOOKUP should be the value you are searching for not a range.

Post an example file and perhaps we can solve it for you

 

[Aladin Akyurek]

Hi,

My current formula is:

=VLOOKUP($G$3:$G$6000,$Q$3:$R$14,2,FALSE)

Please could somebody tell me how I get it to ignore blanks and not come up with #N/A?

Thanks

On Excel 2007 or later...

If a text result is expected, copy down:

[FONT=Lucida Console]=IF($G3="","",IFERROR(T(VLOOKUP($G3,$Q$3:$R$14,2,0)),""))[/FONT]

If a numeric result is expected, copy down:

[FONT=Lucida Console]=IF($G3="","",IFERROR(VLOOKUP($G3,$Q$3:$R$14,2,0),""))[/FONT]

Otherwise...

If a text result is expected, copy down:

[FONT=Lucida Console]=IF($G3="","",LOOKUP(REPT("z",255),CHOOSE({1,2},"",[/FONT]
[FONT=Lucida Console]  VLOOKUP($G3,$Q$3:$R$14,2,0))))[/FONT]

If a numeric result is expected, copy down:

[FONT=Lucida Console]=IF($G3="","",LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,[/FONT]
[FONT=Lucida Console]  VLOOKUP($G3,$Q$3:$R$14,2,0))))[/FONT]

 

[jedibrown]

Perfect - Thank you very much!

 

[jedibrown]

Hi,

I have tried this for another part but everything stays blank.

=IF(A4="","",LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP(A4,Sheet2!A4:M4000,11,FALSE))))

Can anyone help please?

Thanks

 

[jedibrown]

I have also tried this one:

=IF(A5="","",IFERROR(T(VLOOKUP(A5,Sheet2!A5:M4001,11,FALSE)),""))

The same thing happens - It worked perfectly before for the other one.

Thanks

 

[Aladin Akyurek]

Hi,

I have tried this for another part but everything stays blank.

=IF(A4="","",LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP(A4,Sheet2!A4:M4000,11,FALSE))))

Can anyone help please?

Thanks

I have also tried this one:

=IF(A5="","",IFERROR(T(VLOOKUP(A5,Sheet2!A5:M4001,11,FALSE)),""))

The same thing happens - It worked perfectly before for the other one.

Thanks

You probably need to make the ranges absolute (lock)...

=IF($A4="","",LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP($A4,Sheet2!$A$4:$M$4000,11,0))))

=IF($A4="","",IFERROR(T(VLOOKUP($A4,Sheet2!$A$4:$M$4000,11,0)),""))

Note that these formulas expect the VLOOKUP bit return a text value when successful. By the way, 0 does the same as FALSE here.

 

[jedibrown]

Hi - I simply used =if(a4="","",VLOOKUP(A4,Sheet2!A4:M4000,11,FALSE)

Seems to be working perfectly - Thanks again for all of your help

 

[Aladin Akyurek]

Hi - I simply used =if(a4="","",VLOOKUP(A4,Sheet2!A4:M4000,11,FALSE)

Seems to be working perfectly - Thanks again for all of your help

Right. If you copy that formula down, you ordinarily need to lock Sheet2!A4:M4000.

 

[jedibrown]

Brlliant - All woking as it should now.

Thanks!