?Countifs?
If I have a Data Grid (called: Jan ) that has 3 columns ( $, #, %), and 2 Rows (Account #, Sales Rep,) what formula would I use in a separate Data Grid (called: Data) to pull specific columns.
Example: I want to populate the "$" value listed in "Jan" if the Row is "Account #1"
Hoping there is a formula where I can basically ask: "return the $ value from Jan if Account # is equal to 1"
Answers
-
Hi @Drew Cooper
"return the $ value from Jan if Account # is equal to 1" translated into a formula looks like this:
=INDEX({Jan Range $}, MATCH([Account 1]@row, {Jan Range Account #}, 0))
INDEX(range, row_index) returns a range {Jan Range $} of the row specified by row_index.
MATCH( search_value, range, [ search_type ]) returns the row_index of the range ( {Jan Range Account #}) that is equal to the search_value (Account #: 1).
https://help.smartsheet.com/function/match
To show if there is no match, use a formula like this:
=IFERROR(INDEX({Jan Range $}, MATCH([Account 1]@row, {Jan Range Account #}, 0)), "Not Registered")
For details, check the published demo sheet at the bottom.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 429 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!