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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Hannah H
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much Genevieve. This works perfectly. Your clear explanation really helps to cement my understanding.
-
Great! Happy to help 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!