Return a Text Value?

cmitch

New Member
Joined
Aug 15, 2018
Messages
5
Is it possible to look at the 2 text values in these 2 columns and if:
Both columns are #N/A - then return #N/A into a 3rd column
COL B is DNA & COL C is #N/A then return DNA to the 3rd Column
COL B is DO NOT CONTACT & COL C is #N/A then return "DO NOT CONTACT" to the 3rd Column
COL B is #N/A & COL C is Resume Parsed then return "Resume Parsed" to the 3rd Column


COLUMN B COLUMN G
DNA #N/A
DO NOT CONTACT #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A Resume Parsed
#N/A #N/A
DNA #N/A


Thanks!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
With your sample data values for Col_B and Col_G begining in Row_1
This regular formula returns the text from either of those columns if one is text and the other is a #N/A error:
Code:
H1: =IF(AND(ISNA(B1),ISNA(G1)),NA(),IFERROR(B1,"")&IFERROR(G1,""))

Is that something you can work with?
 
Upvote 0
With your sample data values for Col_B and Col_G begining in Row_1
This regular formula returns the text from either of those columns if one is text and the other is a #N/A error:
Code:
H1: =IF(AND(ISNA(B1),ISNA(G1)),NA(),IFERROR(B1,"")&IFERROR(G1,""))

Is that something you can work with?

That worked perfect! Thank You!

If I needed to add one more column - would this be correct?
Code:
=IF(AND(ISNA(B1),ISNA(G1),ISNA(K1)),NA(),IFERROR(B1,"")&IFERROR(G1,"")&IFERROR(K1,""))

Also, I noticed that if neither field has #N/A, it returns both values

I'm attempting to see who on sheets ABC, DEF, GHI, JKL is on XYZ. I need to check by Name, Phone, and Email. So far the only thing I can figure out is to do 3 VLOOKUP on each sheet (ABC, DEF, GHI, JKL). It's possible that someone could be on none or all 4 of ABC, DEF, GHI, JKL as compared to XYZ. If there is a match - it returns a cell from XYZ. Hence the need for the formula above. Is there a better way to accomplish what I'm needing done?
 
Upvote 0
Yes, you could use the formula you posted.
If your actual needs are more complicated, please share ALL of the requirements so you can get a comprehensive solution...instead of incremental solutions.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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
Back
Top