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

kznadalin

Board Regular
Joined
Jun 16, 2010
Messages
53
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:
=IF(ISNA(VLOOKUP(B2,aeriesLoginDate!$A:$G,5,FALSE)), "Needs setup", "CBDC")

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
    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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 

kznadalin

Board Regular
Joined
Jun 16, 2010
Messages
53
@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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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")
 

kznadalin

Board Regular
Joined
Jun 16, 2010
Messages
53

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top