# 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

Tags:

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!