![]() |
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! |
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! |
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! |
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 02:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com