ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   VLOOKUP Condition (https://www.excelbanter.com/excel-discussion-misc-queries/234914-vlookup-condition.html)

lightbulb

VLOOKUP Condition
 
I'm trying to do a Vlookup where if it can't find the information in the
specified array, it returns a predetermined value...is there a way to do this?

Thanks!

JLatham

VLOOKUP Condition
 
Yes, when VLookup() doesn't find a match, it returns #N/A error and you can
test for that:

=IF(ISNA(yourVlookupFormula),predeterminedValue,yo urVlookupFormula)

a real one might look like
=IF(ISNA(VLOOKUP(A1,Sheet2!B9:X109,3,False)),-55,VLOOKUP(A1,Sheet2!B9:X109,3,False))

You could even return text as:
=IF(ISNA(VLOOKUP(A1,Sheet2!B9:X109,3,False)),"No Match
Found",VLOOKUP(A1,Sheet2!B9:X109,3,False))

"lightbulb" wrote:

I'm trying to do a Vlookup where if it can't find the information in the
specified array, it returns a predetermined value...is there a way to do this?

Thanks!


Jim Thomlinson

VLOOKUP Condition
 
=if(isna(vlookup(...), "Predetermined Value", vlookup())

--
HTH...

Jim Thomlinson


"lightbulb" wrote:

I'm trying to do a Vlookup where if it can't find the information in the
specified array, it returns a predetermined value...is there a way to do this?

Thanks!


Mike H

VLOOKUP Condition
 
hi,

2 ways

=IF(COUNTIF(A1:A20,F1)0,VLOOKUP(F1,A1:B20,2,FALSE ),"My Pre defined value")

or

=IF(ISNA(VLOOKUP(F1,A1:B20,2,FALSE)),"My pre defined
value",VLOOKUP(F1,A1:B20,2,FALSE))

Mike

"lightbulb" wrote:

I'm trying to do a Vlookup where if it can't find the information in the
specified array, it returns a predetermined value...is there a way to do this?

Thanks!



All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com