Excel - conditional VLOOKUP formula

[emerald]

Hi there,

I have a VLOOKUP cell which is

=VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE)

Is it possible to put in a condition in this formula so that if an entry is put into C11 that cannot be matched from cells A2:B39 in the 'SchemeList' Sheet then the entry 'BSS' appears in the VLOOKUP cell?

If so, what is the formula I need to use?

Appreciation for any help.

Emerald

"Be kind for everyone
you meet is fighting
a harder battle

 

[SkipVought]

Hi,

[tt]
=if(isna(VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE),"BSS",VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE)

)


[/tt]


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[emerald]

Hi Skip

hv tried what u suggest a few times, but does not work.

however, I am grateful and thank you for taking the time to respond.

best wishes
Emerald

"Be kind for everyone
you meet is fighting
a harder battle

 

[SkipVought]

Exactly what does not work? What result are you getting or not? Please be very specific.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[emerald]

Hi Skip

I made a slight adjustment to the formula and it now works. The brackets were not quite right.

Amended it is

=IF(ISNA(VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE)),"BSS",VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE))

Thanks again

"Be kind for everyone
you meet is fighting
a harder battle

 

[xlbo]

typo from Skip I think:

=if(isna(VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE)),"BSS",VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[emerald]

Hi Skip

Can u tell me how to amend the formula
so that if cell C1 is blank (without any entry), then the formula cell also remains blank?



"Be kind for everyone
you meet is fighting
a harder battle

 

[Gavona]

What have you tried?
Lookup the IF function in help.
You may want to consider conditions such as:
cll=""
or Isblank(c11)

Gavin

 

[SkipVought]

[tt]
=if(isblank(c11),"",if(isna(VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE)),"BSS",VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE))
[/tt]
it's like building blocks.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[emerald]

Hi Skip

Thanks again.

Only needed an extra closing bracket at the end.

=if(isblank(c11),"",if(isna(VLOOKUP
(C11,SchemeList!$A$2:$B$39,2,FALSE))
,"BSS",VLOOKUP(C11,SchemeList!$A$2:$B$39,2,FALSE)))



"Be kind for everyone
you meet is fighting
a harder battle