?Countifs?

Drew Cooper

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"

jmyzk_cloudsmart_jp

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.

https://help.smartsheet.com/function/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.

https://app.smartsheet.com/b/publish?EQBCT=27f731d10de7410e9aca00928c07420a

https://app.smartsheet.com/b/publish?EQBCT=9e15f88868634cf4bfe3673b5aa25174