VLOOKUP/IF or INDEX/MATCH?
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.
Best Answer
-
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:
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
Answers
-
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!
-
Thanks for the info Lewis. Luckily speed isn't an issue here. 😉
-
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:
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
-
Thank you so much Genevieve. This works perfectly. Your clear explanation really helps to cement my understanding.
-
Great! Happy to help 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!