Help with IF function

siow

New Member
Joined
Feb 24, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi I'm hoping someone one can help me
I have an excel spreadsheet which I want to tell me which stage I am in. For instance if text or date is entered in B2 write "Stage 1", if a text or date is entered in cell C2 change to "Stage 2" etc. Here is a copy of the code that I can't get working

=IF(AND(ISTEXT(B2)),"Stage 1"), IF(AND(ISTEXT(C2)),"Stage 2"), IF(AND(ISTEXT(D2)),"Stage 3"),IF(AND(ISTEXT(E2)),"Stage 4"), IF(AND(ISTEXT(F2)),"Stage 5")

Any help would be appreciated :)
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Reverse the order of your IFs. Also, there is no need to use AND when it only has one argument. Also since you are using 365 you can use IFS and a shorter formula.

This assumes that the cells B2:F2 all start empty, then are populated with text (not numbers!) from left to right. Let me know if this works for you.

Excel Formula:
=IFS(ISTEXT(F2),"Stage 5",ISTEXT(E2),"Stage 4",ISTEXT(D2),"Stage 3",ISTEXT(C2),"Stage2", ISTEXT(B2),"Stage 1")
 
Upvote 0
Welcome to the MrExcel board!
You also said that the cells could contain a Date. ISTEXT will return FALSE for that so would not count that as a stage if it is the last entry in the row.

My suggestion to allow for dates or text is:
Excel Formula:
="Stage " & XLOOKUP("??*",B2:F2&"|",{1,2,3,4,5},0,2,-1)
 
Last edited:
Upvote 0
I am also wondering if columns B:F might have the relevant headings and then you could use this?

23 02 25.xlsm
BCDEFG
1Stage 1Stage 2Stage 3Stage 4Stage 5Current Stage
2Done3/04/2022MondayStage 4
3 
43/01/20234/01/20235/01/2023Stage 3
5StartedStage 2
Stage
Cell Formulas
RangeFormula
G2:G5G2=XLOOKUP("??*",B2:F2&"|",B$1:F$1,"",2,-1)
 
Upvote 0
Solution
Hi guys thank u very much for your prompt response, i'm really happy all of these suggestions worked 😊.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
=XLOOKUP("??*",B2:F2&"|"

Why did you include ??* In the beginning and "|" in the end?
Why not just * instead of ??*
I am trying to understand your formula. I would appreciate if you can explain it.
 
Upvote 0
Why did you include ??* In the beginning and "|" in the end?
Why not just * instead of ??*
If you leave out the "|" and just use * as you suggested, it does not pick up the relevant date (numerical) entries & returns incorrect results for those rows.

23 02 25.xlsm
BCDEFG
1Stage 1Stage 2Stage 3Stage 4Stage 5Current Stage
2Done3/04/2022Stage 1
3 
43/01/20234/01/20235/01/2023 
5StartedStage 2
Stage
Cell Formulas
RangeFormula
G2:G5G2=XLOOKUP("*",B2:F2,B$1:F$1,"",2,-1)


So I added "|" (could have been any non-numeric character) to each cell's value to ensure all values were treated as text. Having done that, since I added one character to every value, I needed to ensure that there were at least 2 characters in the result. Hence the inclusion of ??
 
Upvote 0
Thank you for the explanation, Peter.
So what I understood is, you added "|" in the end to convert numbers to text, correct?
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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