If Statement with VLookup

[Chart68]

Hi,

I have trawled through all the questions on vlookup but cant seem to find an answer similar to what I'm trying to achieve.

Here goes

I'm looking up a cell which will either hold a value or be blank, if the cell holds a value I want to output a 4 (not the value in the cell) but if it is blank I need to output blank.

=IF(VLOOKUP(E2,staffdata!B:K,10,FALSE),4,"") this works where there is a value but I get #VALUE if the cell is blank.

Im sure this is a simple fix but my brain has shut down.

Thanks lots

Claire

 

[coolkiran]

Hello

Formula is working for both conditions, Small query : You referred if cell value is blank, let me know which column?

Cheers
Kiran

 

[Chart68]

Hi thanks, Im looking at column K

 

[NARAYANK991]

Hi ,

I tried out the following formula :

=VLOOKUP(E2,staffdata!B1:K10000,10,FALSE)

under the following conditions :

1. E2 blank - the result is #N/A.

2. E2 value , but not found in staffdata!B1:K10000 - the result is #N/A.

3. E2 value , found in staffdata!B1:K10000 , corresponding cell in column K ( column 10 ) blank - result is 0

4. E2 value , found in staffdata!B1:K10000 , corresponding cell in column K ( column 10 ) not blank - result is the corresponding value in column K.

I do not know which cell you are referring to ; having given an example with E2 as the lookup value , let me assume E2 is blank ; this is the same as case 1 above.

For this , you can use :

=IF(ISNA(VLOOKUP(E2,staffdata!B1:K10000,10,FALSE)),"",4)

Suppose we assume you are referring to case 3 ; for this , use :

=IF(VLOOKUP(E2,staffdata!B1:K10000,10,FALSE)=0,"",4)

Using staffdata!B:K is not advised. Always use either big enough ranges , or dynamic ranges , never entire column references.

Narayan

 

[koi]

Hi,

you can try IFERROR(IF(VLOOKUP(E2,staffdata!B:K,10,FALSE),4,""),"")

 

[Chart68]

Hi all,

Sorry for the delay in getting back to you all but I couldn't get the above to work using the field I was looking up, (E2) but I had a corresponding field in D2 which seems to give me the output I was looking for.

Thanks all for your help.

Claire