VLOOKUP/IF or INDEX/MATCH?

Hannah H

Hello,

I'm not sure which combination of formulas I require (VLOOKUP, IF, INDEX, MATCH...)

In sheet 1 I have the columns "resource name" (text) and "onboarding paid?" (check box)

In sheet 2 I have the columns "resource name" (text), "onboard?" (check box) and "milestone paid?" (check box)

If the resource name matches, AND "onboard?" in sheet 2 is checked, I want to return the value of "milestone paid?' from sheet 2, into "onboarding paid?' in sheet 1.

It's adding in the second criterion that has me flummoxed.

Genevieve P.

Hi @Hannah H

Close! The "ranges" are actually just one column each, they shouldn't be spanning across multiple columns. I'll outline what each range should be referencing:

1 . {Milestone Paid Column}

Your first range should be what you want pulled, or brought in. In this case, it's the Milestone Paid Column. Just the column selected on its own. I'm going to write this as {Milestone Paid Column}

2 . {Resource Name Column}

Your second range is just the one column, the "Resource Name" column in your second sheet.

3 . {Onboard? Column}

Your third range is just the one column, the "Onboard?" column in your second sheet.

Final touches:

You are absolutely correct in using 1 to indicate if it's checked or not, however one of your criteria is to match the Resource Name. In this instance, you will want to list the Resource column reference (as in point 2 above), but then your criteria can look at the cell in this 1st sheet, in the Resource column: [Resource Name]@row

Try this:

=INDEX(COLLECT({Milestone Paid Column}, {Resource Name Column}, [Resource Name]@row, {Onboard? Column}, 1), 1)

Here are some Help Articles you may find useful:

• INDEX Function
• COLLECT Function
• Using @row
• Cross Sheet References
• Referencing Columns

Let me know if you still need help! If so, it would be good to see a screen capture (but please block out any sensitive data).

Cheers,

Genevieve