for two criteria query should I use LOOKUP or INDEX MATCH

natasha ross

I need to populate the low range based on a  match to a specific specialty and tier.  The specialty is just a drop down but the Tier was based on the following IF formula:

=IF([Points Total]11 <= 15, 3, IF([Points Total]11 >= 16, 2, IF([Points Total]11 <= 45, 2, IF([Points Total]11 >= 46, 1))))

• Question 1: Should  " " around the tiers?  
• Question 2: Should I use Index Match or Lookup to populate the Low Range?
• Question 3: Depending on which one is the right one to use, please look at the formulas below.  What do I need to fix?

 1. Here is the index Match but it doesn't work: error is "# invalid Data Type"

• =INDEX($[Consultant City]1:$[Consultant City]7, MATCH([Consultant Specialty]11, $[Consultant First name]1:$[Consultant First name]7, AND(MATCH(Tier11, [Consultant last name]1:[Consultant last name]7, 0))))

2. Here is the LOOKUP but it doesn't work: error is "unparseable"

• =LOOKUP([Consultant Specialty]12, [Consultant First name]1:[Consultant State]7 AND( LOOKUP(Tier12, [Consultant First name]1:[Consultant State]7, 3,false

 

 

Robert S.

Hello,

 

Thanks for the question. Currently these types of formulas don't support multiple criteria look up. If you'd like for this feature to be added in the future, I recommend submitting a Product Enhancement Request using the link in the to the right in the "Quick Links" section.

 

Although this functionality isn't currently available, there is a workaround that may work for you. This workaround involves adding a column to the sheet with a formula that combines the "Consultant First name" and "Consultant last name" columns for each row in this table. The formula would look like this:

 

=[Consultant First name]2 + [Consultant last name]2

 

This can be placed in the new column in row 2 and drag-filled down for the rest of the rows in the table. More on drag-fill can be found here if needed (https://help.smartsheet.com/articles/518318#dragfill). This should then combine the values of these two columns into one, for instance row 2 would show "Dermatology1". Once this is set, you can hide this column if you'd like and then use either a LOOKUP() function or the combined INDEX() and MATCH() to look at this new column. Here's how these two could be written:

 

=LOOKUP([Consultant Specialty]9 + Tier9, [Combined Column]2:[Consultant City]7, 4, false)

 

=INDEX([Consultant City]2:[Consultant City]7, MATCH([Consultant Specialty]9 + Tier9, [Combined Column]2:[Combined Column]7, 0))

 

Either of these will work to accomplish the same goal, however the LOOKUP() function requires that the "search_value" be in the leftmost column (position 1) of "lookup_table". If the new column you create to combine the values is not the leftmost column in the lookup table, it will either need to be moved to be the leftmost column or you can use the INDEX() / MATCH() formula.

 

As for the quotations, those are only needed around text values. Number values don't need quotations even if it's the result of a formula.