If Function - 4 results needed

TrishaL

Board Regular
Joined
Jul 9, 2013
Messages
56
Office Version
  1. 2019
Platform
  1. Windows
Hi

Needing help with this. What i have is not working, obviously. Below are 4 scenarios and I need results for all 4.
I have this =IF(ISNUMBER(SEARCH("*",AN32)),IF(AO32>0,"Yes","Pending"),"") from my spreadsheet but I don't know how to change it for what i now need below. What i have below in the "D (Results)" column is the answer i want the formula to give me.
Any help would be appreciated.
Thanks
[TABLE="width: 652"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD][TABLE="width: 651"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D (RESULTS)
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]38290-IOS-PC9540
[/TD]
[TD]30-Aug-18
[/TD]
[TD]TRUE
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]38290-IOS-PC9541
[/TD]
[TD]30-Aug-18
[/TD]
[TD]FALSE
[/TD]
[TD]Pending
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]38290-IOS-PC9542
[/TD]
[TD]
[/TD]
[TD]FALSE
[/TD]
[TD]Pending
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]"empty cell"
[/TD]
[TD]"empty cell"
[/TD]
[TD]FALSE
[/TD]
[TD]No or leave empty
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 652"]
<colgroup><col><col span="4"></colgroup><tbody></tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Why this ISNUMBER SEARCH why not just check for AN32<>""

Why is your formula referring to cells AN32 and AO32 when your range is in A:C?

Based on your sample though:
=IF(C2,"Yes",IF(COUNTA(A2:B2),"Pending","No"))


Excel 2010
ABCD
1ABCD (RESULTS)
238290-IOS-PC954030-Aug-18TRUEYes
338290-IOS-PC954130-Aug-18FALSEPending
438290-IOS-PC9542FALSEPending
5FALSENo
Sheet1
Cell Formulas
RangeFormula
D2=IF(C2,"Yes",IF(COUNTA(A2:B2),"Pending","No"))
D3=IF(C3,"Yes",IF(COUNTA(A3:B3),"Pending","No"))
D4=IF(C4,"Yes",IF(COUNTA(A4:B4),"Pending","No"))
D5=IF(C5,"Yes",IF(COUNTA(A5:B5),"Pending","No"))
 
Last edited:
Upvote 0
Can you supply more info as to your rules for each scenario and how you arrive at the result
 
Upvote 0
I copied the formula from my spreadsheet -- those are the columns/rows my data is actually in. sorry about that.
 
Upvote 0
Column
A - needs data
B - needs a date
C - will be True or False
D - If A and B have data and C is True, then return "Yes". If A and B have data and C is False, then return "Pending". If A has data but B is empty and C is False then return "Pending". The last scenario is A is empty, B is empty and C is False, then return "No" or just return blank cell.
Thanks
 
Upvote 0
I have tried what you provided but this won't exactly work as my columns aren't actually next to each other.
A is actually on AT
B is actually on AZ
C is actually on BI

Thanks,
 
Upvote 0
It should, just adjust the ranges:

=IF(BI2,"Yes",IF(COUNTA(AT2,AZ2),"Pending","No"))


Excel 2010
ATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJ
1
238290-IOS-PC954030-Aug-18TRUEYes
338290-IOS-PC954130-Aug-18FALSEPending
438290-IOS-PC9542FALSEPending
5FALSENo
Sheet1
Cell Formulas
RangeFormula
BJ2=IF(BI2,"Yes",IF(COUNTA(AT2,AZ2),"Pending","No"))
BJ3=IF(BI3,"Yes",IF(COUNTA(AT3,AZ3),"Pending","No"))
BJ4=IF(BI4,"Yes",IF(COUNTA(AT4,AZ4),"Pending","No"))
BJ5=IF(BI5,"Yes",IF(COUNTA(AT5,AZ5),"Pending","No"))
 
Upvote 0
Hi
Thank you for the response. So I figured out why this is not working. My data is taken from Access to Excel. In the table you put about for the scenario in Row 5 the result i was getting was "Pending" which was not the desired result. I have found that if i go to column AT and delete the empty cells then the formula works and returns a "No". For some reason it thinks there is data there and there isn't. Is there a way around this? I could always filter blank cells in this column and then delete. Let me know if you have any ideas. Thank you so much!
 
Upvote 0
=IF(BI2,"Yes",IF(AT2&AZ2="","No","Pending"))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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