Multiple Criteria for INDEX/MATCH or VLOOKUP?

Jennifer Whitlock · 08/04/20

Hello,

I have been defeated while trying to reference another sheet and pull information that matches multiple criteria. I need to pull an Account Number that matches both a Vendor Name and Location Name. Essentially, I want to populate the account numbers for specific locations based on a particular vendor. I cannot add a "Helper Column" to the original sheet that I am referencing. Is there a way to accomplish this using either a VLOOKUP or INDEX/MATCH formula? Any help would be greatly appreciated.

Thanks!

Paul Newcome · 08/05/20

Hmm... It almost sounds like a bug. How about this...

Delete the formula entirely. Log out. Clear your browser's cookies and cache. Log back in. Re-enter the formula.

L_123 · 08/04/20

try using the collect formula

=index(collect(return RANGE, criteria RANGE 1, criteria 1, criteria RANGE 2, criteria 2),1)

Jennifer Whitlock · 08/04/20

@L@123 I have tried the combination of INDEX and COLLECT and cannot for the life of me understand what I am doing wrong.

Here is the formula I have currently:

=INDEX(COLLECT({Full Range}, {Clinic Name}, Clinic@row, {Vendor Zoetis}, "Zoetis"), 1)

But it returns a #INCORRECT ARGUMENT SET error.

Jennifer Whitlock · 08/05/20

Posting in hopes that someone may be able to help!

L_123 · 08/05/20

Ok, the syntax is correct for your formula, but there could potentially be an issue with the ranges. Make sure to double check all of your ranges have the same number of cells (smartsheet sometimes has a glitch where it deletes ranges as you save them, after you've made them they are fine, but it is good to double check)

If you are interested in how the formula functions, there is an explanation below.

So the collect formula returns a set of values based on a criteria (or a 1d array if you know any coding languages). Basically you treat the collect formula as a range for other formulas. The first part of the collect formula is the return, or the values you want inside of your range. Next is the range you want to filter, and after that is the criteria.

example:

colA colB

A 1

B 1

C 2

A 2

B 3

C 3

=join(collect(colA1:colA6,colB1:colB6,@cell = 2

In the formula above, I use join to show the contents of the collect. The return for this formula will be CA. This is because I am returning (first criteria for collect formula) the values of colA where colB (second criteria for collect formula) is equal to 2 (Third criteria for collect formula). If you have more criteria you want to filter by, you can justt keep adding ranges and criteria to the end of the collect formula, as you did in your own formula for a 2 criteria problem. Another example:

=join(collect(colB1:colB6,colA1:colA6,@cell = "A"

This formula will return the values in colB where colA is equal to "A"

The collect formula is one of the most versatile formulas inside smartsheet, but it needs help from another formula in order to work. Above I have used the join, but you can also use COUNT, SUM, INDEX,SUBSTITUTE, and many other formulas. In your example INDEX is used to return the first value out of the collect formula. Join potentially could work just as well.

Paul Newcome · 08/05/20

I agree with @L@123. I believe it may be an issue with the range sizes.

This Range: {Full Range}

Should only be the column you are trying to pull from and not include the other columns/ranges referenced.

Jennifer Whitlock · 08/05/20

@Paul Newcome I corrected the range name in my sheet earlier because I realized it could be confusing to someone else, it only references a column with the account numbers in it. I will double and triple check the range sizes, but they should all be the same.

Paul Newcome · 08/05/20

When you select the ranges, are you clicking on the column header to select the entire column?

Is that error present in the source data?

Jennifer Whitlock · 08/05/20

@Paul Newcome Yes. I wouldn't consider myself a new user, I've McGyvered some long and complex functions in the past. I'm really not sure what is causing this one to fail.

Could it be that there are parent rows in the source sheet? Just exploring all possibilities at this point.

Thanks for trying to help me iron this out!

Paul Newcome · 08/05/20

Hierarchy shouldn't affect this particular formula. Did you check the source data to see if that error was present anywhere and is pulling through?

L_123 · 08/05/20

How large is the source sheet? I've found collect can get a bit wonky when you get into the 15k row range and use other sheet references in it and can throw some errors before the program catches up.

Jennifer Whitlock · 08/05/20

@Paul Newcome The only error on the source sheet is a #NO MATCH error in a column that is not being referenced.

@L@123 The source sheet is 2400 Rows and 16 columns

Paul Newcome · 08/05/20

Are all ranges referencing the same sheet?

Jennifer Whitlock · 08/05/20

@Paul Newcome Yes. There are only two sheets in play here. A source sheet that the ranges are referencing and a sheet where the formula is being used.

Paul Newcome · 08/05/20

Hmm... It almost sounds like a bug. How about this...

Delete the formula entirely. Log out. Clear your browser's cookies and cache. Log back in. Re-enter the formula.

Jennifer Whitlock · 08/05/20

So this formula is working now:

=INDEX(COLLECT({Account Number}, {Clinic Name}, Clinic@row, {Vendor Zoetis}, "Zoetis"), 1)

There's still a few hiccups but I'm going to double check, as I think it may just be a typo on our end.

Thank you both so much! I think ultimately it was just a bug.

Formulas and Functions

Formulas and Functions

Multiple Criteria for INDEX/MATCH or VLOOKUP?

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA