Matching multiple criterias

Jakob Henry

Hello everyone,

I have a question on how I can use a vlookup to look up to parameters that bouth need to match to then give me a third parameter.

I had to remove all specific company terms so this screenshots are now very generic.

This is the formula I used: =IF({Sheet1} = "Country6"; [Assigned To]21; AND(VLOOKUP([Promo Type]@row; Customer1:[Assigned To]20; 6; false) VLOOKUP([Promo Type]26; Customer1:[Assigned To]20; 6; false))

The aim is to check if the country in sheet 1 is "Country6", if so then give the name in cell "Assigned To 21". If not then check the whole table if the customer is the customer in cell "Promo Type 24" and the promo type is the promo type in cell "Promo Type 26". If so give me the name in "Assigned To".

The ";" are right, because I use the German version of Smartsheet which uses ";" instead of ",".

Is it even possible o do that I want to do? Am I just using the wrong formula or what else could be my problem?

Nick Korna

Hi @Jakob Henry,

You would be able to use an INDEX & COLLECT combination for this latter section (you may need to amend the commas to semi colons):

=INDEX(COLLECT([Assigned To]1:[Assigned To]22,[Customer]1:[Customer]22,[Promo Type]24,[Promo Type]1:[Promo Type]22,[Promo Type]26),1)

However you are going to have some issues with how some of this data is presented - specifically rows 20-21 - you would probably want to have a cross sheet INDEX(COLLECT) checking vs. Customer & Country and then giving the name from your table.

Alternatively, you could port the list of country 4/6 customers into your data chunk (and amend the ranges).

Apologies if this latter bit is a bit vague - I am unsure if your country 4/6 data is in a single sheet or 2 separate ones (or how exactly it is arranged).

Jakob Henry

Hi Nick,

that sounds like a possible solution.

I tried going another way by adding in a helper column in which I combined Customer and Promo Type. I also created a helper cell in which i combined "Promo Type 24" and "Promo Type 26". So now my vlookup is looking to match the helper cell with the helper column. But unfortunately I get the result #NO MATCH, which makes no sense because I am certain that the formula is right an the Names in the helper cell and in the helper column are the same. So there should be a match.

Any idea why this could be?

If I can't find a solution I might need to use INDEX & COLLECT. But I would have to have a closer look at the functions first, because I never used them before.

Nick Korna

You would also need a helper column to be able to use the helper cell (somewhere to the left of Name as this would be the column reference).

Sample:

Here the Helper column's formula is pretty simple:

=Customer@row + [Promo Type]@row

The search values are going in Customer 11 & 12 (Customer and Promo Type respectively), and Customer 15 has this formula in:

=Customer11 + Customer12

Assigned to 15 is:

=VLOOKUP(Customer@row, Helper1:[Assigned To]6, 5)

You could also change the VLOOKUP to an INDEX, which would allow the helper to be positioned as a final column etc. instead.

For the Country 4/6 customers, you could put one of the above formulas as part of a nested IF/IFERROR - if the customer & promo can be found using the first formula great, if not, it moves onto the next one.

For example, using the INDEX(COLLECT above) to check on your original table plus a theoretical sheet containing only Country 4 customer data:

=IFERROR(INDEX(COLLECT([Assigned To]1:[Assigned To]19,[Customer]1:[Customer]19,[Promo Type]24,[Promo Type]1:[Promo Type]19,[Promo Type]26),1),IF(COUNTIF({Country 4 Customer column},[Promo Type]24)>1,[Assigned To]20,[Assigned To]21)

This would check both customer & promo type for any combination of data that would appear in rows 1-19 when entered in the relevant cells (Promo Type 24 & 26) and give the name.

If it couldn't be found, it would look for the customer only (Promo Type 24) to match with the customer list in Country 4's sheet customer column and if found would give the name found in [Assigned To]20 - the name assigned to Country 4.

Anything not found will return the name in [Assigned To]21 - as Name 1 has both Country 6 & any other customers. It may lead to some false positives though - any errors will also show up as Name 1!

If you have additional promotion types for the other countries, you would need a more complex formula to check the additional criteria.

Hopefully this helps give you some ideas though, let me know if you need any more advice/clarification!

Jakob Henry

Hi Nick,

That helped a lot. I made it work.

Thank you very much and have a nice day!

Nick Korna

No problem, glad to have helped! 😊