# VLOOKUP/IF or INDEX/MATCH?

Options
✭✭✭✭✭

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.

Tags:

• Employee
Options

It sounds like you'll want to build an INDEX(COLLECT to look for multiple criteria to match. Paul has a great example of this on another post (see here).

Take a look and let me know if you need help building this out!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭
Options

Thanks for your reply Genevieve. I've had a look at Paul's formula and had a go, but I'm not 100% sure what I'm doing!

Here is Pauls formula: =INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

Here is my attempt:

=INDEX(COLLECT({Resource Name}, {Sheet 2 Range 1}13, =1, {Sheet 2 Range 2}11, =1), 1)

Resource Name is in sheet 1

Sheet 2 Range 1 & 2 have the first column as Resource Name

Position 13 is "onboard?"

Position 11 is "milestone paid?"

Am I correct using =1 to find the check in the checkbox?

Thanking you!

• ✭✭✭✭✭
Options

Thanks for the info Lewis. Luckily speed isn't an issue here. 😉

• ✭✭✭✭✭
Options

Thank you so much Genevieve. This works perfectly. Your clear explanation really helps to cement my understanding.

• Employee
Options

Great! Happy to help 🙂