Index match formula

Samuel Dowdy Jr.

=INDEX({MASTER - Customer Invoice Log - Status}, MATCH({MASTER - Customer Invoice Log Profit Center}, [Profit Center]@row, {MASTER - Customer Invoice Log Inv Year}, [Invoice YEAR]@row, {MASTER - Customer Invoice Log Inv Month}, [Invoice MONTH]@row, 0))

Can someone tell what Im doing wrong. The column im trying to match is a dropdown column

Dan Curl

MATCH(search_value, range, [search_type])

Samuel — the Match function is incorrect in your example.

Two main issues

1. You have your Search Value and Search Range flipped.
2. You are trying to match on multiple conditions.

Search Value/Range Flipped

You have

MATCH({MASTER - Customer Invoice Log Profit Center}, [Profit Center]@row

It should be

MATCH([Profit Center]@row,{MASTER - Customer Invoice Log Profit Center}

[Profit Center]@row = The Search Value

{MASTER - Customer Invoice Log Profit Center}= The Search Range

You are trying to match on multiple conditions.

As far as I know you cannot match on multiple conditions.

What you would need to do is create a MatchingID Column that combines the three fields you are trying to match.

In your Customer Invoice Log create a new column that Combines Profit Center, Invoice Year, and Invoice Month.

Then in your sheet where you are using this match you would use a formula like….

INDEX({MASTER - Customer Invoice Log - Status},

MATCH([Profit Center]@row+":"+[Invoice YEAR]@row+":"+[Invoice MONTH]@row,{MASTER - Customer Invoice Log Combined},0),1)

This assumes that your combined column in your Master Invoice Log uses ":" as a delimiter.

Hope this helps

Samuel Dowdy Jr.

Im getting #Incorrect Arguement Set

Dan Curl

MATCH(search_value, range, [search_type])

Samuel — the Match function is incorrect in your example.

Two main issues

1. You have your Search Value and Search Range flipped.
2. You are trying to match on multiple conditions.

Search Value/Range Flipped

You have

MATCH({MASTER - Customer Invoice Log Profit Center}, [Profit Center]@row

It should be

MATCH([Profit Center]@row,{MASTER - Customer Invoice Log Profit Center}

[Profit Center]@row = The Search Value

{MASTER - Customer Invoice Log Profit Center}= The Search Range

You are trying to match on multiple conditions.

As far as I know you cannot match on multiple conditions.

What you would need to do is create a MatchingID Column that combines the three fields you are trying to match.

In your Customer Invoice Log create a new column that Combines Profit Center, Invoice Year, and Invoice Month.

Then in your sheet where you are using this match you would use a formula like….

INDEX({MASTER - Customer Invoice Log - Status},

MATCH([Profit Center]@row+":"+[Invoice YEAR]@row+":"+[Invoice MONTH]@row,{MASTER - Customer Invoice Log Combined},0),1)

This assumes that your combined column in your Master Invoice Log uses ":" as a delimiter.

Hope this helps

Samuel Dowdy Jr.

What if I have a no match? How do I get it to say something else?

Dan Curl

https://community.smartsheet.com/discussion/comment/460530#Comment_460530

Wrap your Formula in IFERROR.

=IFERROR(<Index/Match Formula Here>,"Error Message")