VLOOKUP/IF or INDEX/MATCH?

Hannah H · 08/05/20

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. · 08/06/20

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 theMilestone 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

Genevieve P. · 08/06/20

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

Hannah H · 08/06/20

Hi @Hannah H<\/a> <\/p>

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<\/a>). <\/p>

Take a look and let me know if you need help building this out!<\/p>

Cheers,<\/p>

Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"Hannah H\",\"userID\":32236}}},{\"insert\":\" \\nIt 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 (\"},{\"attributes\":{\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/69141\\\/can-you-use-2-match-criterias-in-an-index-match-formula#latest\"},\"insert\":\"see here\"},{\"insert\":\"). \\nTake a look and let me know if you need help building this out!\\nCheers,\\nGenevieve\\n\"}]","format":"rich","dateInserted":"2020-08-06T09:41:17+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-27T07:46:30+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/258952#Comment_258952","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/258952#Comment_258952

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!

Hannah H · 08/06/20

With unsorted data, VLOOKUP and INDEX-MATCH have about the same calculation times. ... With sorted data and an approximate match, INDEX-MATCH is about 30% faster than VLOOKUP. With sorted data and a fast technique to find an exact match, INDEX-MATCH is about 13% faster than VLOOKUP.<\/p>","bodyRaw":"[{\"insert\":\"With unsorted data, VLOOKUP and INDEX-MATCH have about the same calculation times. ... With sorted data and an approximate match, INDEX-MATCH is about 30% faster than VLOOKUP. With sorted data and a fast technique to find an exact match, INDEX-MATCH is about 13% faster than VLOOKUP.\\n\"}]","format":"rich","dateInserted":"2020-08-06T11:55:04+00:00","insertUser":{"userID":124088,"name":"lewis hamilton","url":"https:\/\/community.smartsheet.com\/profile\/lewis%20hamilton","photoUrl":"https:\/\/community.smartsheet.com\/applications\/dashboard\/design\/images\/banned.png","dateLastActive":"2020-08-12T11:25:58+00:00","banned":1,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/258960#Comment_258960","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/258960#Comment_258960

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

Genevieve P. · 08/06/20

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 theMilestone 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

Hannah H · 08/06/20

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

Genevieve P. · 08/09/20

Great! Happy to help

VLOOKUP/IF or INDEX/MATCH?

Best Answer

Answers

Help Article Resources

Categories