Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Index & Match Formula to provide row number if found in column
=IF(INDEX(ColumnA:ColumnA,MATCH(ColumnB@row, ColumnB:ColumnB,0), INDEX(Auto:Auto, MATCH(Auto@row,Auto:Auto, 0), "")))
Auto = System column to pull row number
ColumnA =Text
ColumnB =Text
ColumnC= Formula location & Row Number Output
So, what I’m trying to achieve is to have a formula to Lookup ColumnA text and match ColumnB text. If there is not a match, it returns blank, but if there is a match, to return the Auto of ColumnA.
I’m sure I’m overlooking something easy here, as I’m receiving an #INCORRECT ARGUMENT but I would be welcome for your assistance!
Best Answer
-
Try this:
=IFERROR(INDEX(Auto:Auto, MATCH(ColumnB@row, ColumnA:ColumnA, 0)), "")
Answers
-
Try this:
=IFERROR(INDEX(Auto:Auto, MATCH(ColumnB@row, ColumnA:ColumnA, 0)), "")
-
Thanks Paul! You always know how to figure these types of fixes! lol
I dropped it in, and it's returning "1" for all of the values expect the one that matches. Is there an easy fix to make sure that the column doesn't return any value? (Blank)
-
Hi Paul! Nevermind, I figure it out! lol
I've been staring at computer screens too much! Thank you again for your amazing help!😅
-
Hi:
I came across the same problem. I want to know the relative position in a parent/children tree of a project
So instead of looking up the row in a helper column, I am searching for the match of the Auto-Number (called RowID in my spreadsheet)
MATCH(RowID@row, RowID:RowID, 0) is the momentary position of the child (the row itself)
MATCH(PARENT(RowID@row), RowID:RowID, 0)) is the momentary position of the associated parent row
As the RowID is unique by design and autogenerated, I am sure I am not screwing it up like a helper column.
If there is a sneakier way of circumventing smartsheet limitations of spreadsheet functions, then well I have not found it yet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
