# Need help with using IF(ISNA(VLOOKUP....) to return value of cell or "needs setup"

##### Board Regular
Hi, everybody,
I know how to use IF(ISNA(VLOOKUP....) to return message A or message B, but I'd like to return the actual content from the cell looked up if it's there.

Right now, I have this formula:

Instead of "CBDC" I would like the exact date that appears from the other sheet if a date is present. The rest of this formula does the job. I'm including a screenshot. A2 shows the problem of using the formula shown above. A3, A4, A5, and A6, show what I want returned if possible. (A3 also has conditional formatting for the "needs setup," a separate topic. I already know how to do that.)

I appreciate any help with this.

Thanks,
K.

#### Attachments

• Screen Shot 2021-12-01 at 9.10.16 AM.png
73.8 KB · Views: 7

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### jasonb75

##### Well-known Member
Sounds like you just want
Excel Formula:
``=IFERROR(VLOOKUP(B2,aeriesLoginDate!\$A:\$G,5,FALSE),"Nedds setup")``

ISNA(VLOOKUP is a pointless waste of system resource processing the precedent range, you should use ISNA(MATCH( on the left hand column if you only want to test for presence.

##### Board Regular
@jasonb75 , that is very helpful. I have a follow-up question:
Is there some way to deal with a zero when it's listed instead of a date? A few students are listed with a zero [0] instead of blank, N/A or not being listed. When I use the formula you gave me, it returns 1/0/00 if the value on the aeriesLoginDate worksheet is 0. I would like any student with "0" under last login OR any student with N/A or not listed to return as "needs setup." ??

#### jasonb75

##### Well-known Member
This will return anything that doesn't contain a valid date as "Needs setup", does that work?

Excel Formula:
``=IFERROR(1/(1/VLOOKUP(B2,aeriesLoginDate!\$A:\$G,5,FALSE)),"Needs setup")``

##### Board Regular

That looks perfect! Thank you.
BTW, if you have time (and only if), please share what the function is of the (1/(1/VLOOKUP...) I've never seen this before.
Thanks so much!

#### jasonb75

##### Well-known Member
The 1/(1/vlookup part forces the formula to return an error when the result is not a number other than 0.

Dividing 1 by any non 0 number will still give a value, dividing 1 by the result of the first calculation returns to the original value. If the starting value was 0, text, or an error then it will trigger another error and the lookup result is ignored in favour of the error trap.

##### Board Regular
Good to know! Thank you so much!

Replies
1
Views
144
Replies
0
Views
212
Replies
1
Views
322
Replies
5
Views
195
Replies
4
Views
242

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,471
Messages
5,831,844
Members
430,089
Latest member
beesz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back