Index/Match where index is across 2 columns
Hi,
I currently have a simple Index/Match to pull in the hourly rate from a source sheet when the freelancer's name matches. Some freelancers may work on a fixed rate instead. I would like to build a formula that indexes the fixed rate column in the source sheet, if the hourly rate column in the source sheet is blank. I'm struggling to figure out how to review if the source sheet has a blank.
Here is my basic Index/Match
=INDEX({Hourly Rate}, MATCH([Freelancer's Name]@row, {Name}, 0))
Any ideas?
Hannah
Best Answer
-
Hey @Hannah H
To see if I understand, If the source sheet {Hourly Rate} is blank, Index/match {Fixed Rate} else Index/Match {Hourly rate}?
If I got that right, one solution is
=IF(COUNTIFS({Hourly Rate}, @cell<>"", {Name}, [Freelancer's Name]@row)>0, INDEX({Hourly Rate}, MATCH([Freelancer's Name]@row, {Name}, 0)), INDEX({Fixed Rate}, MATCH([Freelancer's Name]@row, {Name}, 0)))
Kelly
Answers
-
Hey @Hannah H
To see if I understand, If the source sheet {Hourly Rate} is blank, Index/match {Fixed Rate} else Index/Match {Hourly rate}?
If I got that right, one solution is
=IF(COUNTIFS({Hourly Rate}, @cell<>"", {Name}, [Freelancer's Name]@row)>0, INDEX({Hourly Rate}, MATCH([Freelancer's Name]@row, {Name}, 0)), INDEX({Fixed Rate}, MATCH([Freelancer's Name]@row, {Name}, 0)))
Kelly
-
Thank you Kelley, that solved my problem.
Can you explain to me how the countifs is working here?
Thanks!
-
Hey Hannah
Gladly. If all of the formulas were on the same sheet, it would be straight-forward to write an IF statement that said If this cell is blank, do this, else do that. Because we are looking cross-sheet, we cannot use a plain IF statement to check the cross-sheet cells. We have to use a function that does work which is why I chose a COUNTIFS.
Our COUNTIFS criteria limits what is being counted thus we are able to filter to cells that aren't blank (@cell<>"") that also match your Freelancer's name. If the count for these non-blank cells is greater than zero, (that are also matching your Freelancer's name at row), it means the cell wasn't blank. A zero count would mean it couldn't find any cells that weren't blank. By using the count this allows us then to check " If this cell is blank, do this, else do that"
Did I make sense?
Kelly
-
Yes Kelly, that makes sense - thank you so much for taking the time to explain!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!