Return the last text value from a multi-column search
I have 5 columns from left to right: 'Site 1', 'Site 2', 'Site 3', 'Site 4', 'Site 5'.
I am looking to search each column by row to see if there is a value entered into each site column. If a value is entered, I'd like to return the last site that has a value into a new column labeled 'Latest Site' (e.g. Site 1 and 2 have values so then the Latest Site column returns the value from Site 2).
The site columns would be text fields. Below is an example of what should happen. Could you help with a formula that could be used in the Latest Site column?
Answers
-
@Charles Stiegemeier It isn't pretty, but if you are just checking right to left, you could try:
=IF(NOT(ISBLANK([Site 5]@row), [site 5]@row, if(NOT(ISBLANK([site 4]@row, [row 4], IF(NOT( ... ETC ETC
Cheers,
dm
-
Try this:
=INDEX([Site 1]@row:[Site 5]@row, COUNTIFS([Site 1]@row:[Site 5]@row, @cell <> ""))
-
I see you marked both responses as not answering your question. Can you clarify what didn't work for you? Are you getting a formula error or an incorrect result?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!