Index/Match where index is across 2 columns

Hannah H

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

Kelly Moore

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

Kelly Moore

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

Hannah H

Thank you Kelley, that solved my problem.

Can you explain to me how the countifs is working here?

Thanks!

Kelly Moore

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

Hannah H

Yes Kelly, that makes sense - thank you so much for taking the time to explain!