Matching 2 Criteria in Source sheet to reference 2 other Criteria in Target Sheet

RotFraught · 11/09/24

Hi, I'm trying to use 2 variable data points in a Target sheet to pull 1 data point from the Source sheet that shares the same Criteria and just got stuck, don't regularly use INDEX formula and looking for help - hope the below is detailed enough:

Source Sheet:

Source Sheet has a a "Supplier" column and "HV Breaker Supplier INDEX" column. I want to pull the "Leadtime - DAYS" column data that matches when the Target Sheet Supplier and HV Breaker Supplier INDEX" columns match.

I also need the formula to work on multiple combinations so figured a "AND" would be needed but none of my formulas are working. I'd share them but know they are way off - any help is appreciated:

SOURCE SHEET: Want to pull "Leadtime - DAYS" when the Target Sheet drop-down (HV Breaker Supplier shown in the Target Sheet) matches the same helper columns (Column Match 1 & Column Match 2). For example - "Josh's Breakers" Supplier + "2" HV Breaker Supplier INDEX = would pull "140". NOTE - HV Breaker Supplier INDEX is a IF(AND formula itself that references the LOW and HIGH Range to assign a number 1 thru 6

TARGET SHEET: When the Source Sheet's Column Match 1 and Column Match 2 align - it pulls in the Data (Leadtime - DAYS).

Some examples of the variability - Source "Supplier" and Target "HV Breaker Supplier" (Both Column Match 2) = "Josh's Breakers" and "HV Breaker Supplier INDEX" of the Source and Target (Both Column Match 1) = 3, then "HV Breaker Supplier LT" in the Target pulls in 210.

Same formula would allow Source "Supplier" and Target "HV Breaker Supplier" (Both Column Match 2) = "Brandon's Breakers" and "HV Breaker Supplier INDEX" of the Source and Target (Both Column Match 1) = 1, then "HV Breaker Supplier LT" in the Target pulls in 105.

jmyzk_cloudsmart_jp · 11/12/24

@RotFraught

The last comment suggests the issue lies in the data type of the HV Breaker Supplier INDEX column in the Source Sheet, which likely caused the INDEX(COLLECT) function to fail. Here's how the data type mismatch can explain and resolve the issue:

Revised Explanation

The HV Breaker Supplier INDEX column in the Source Sheet is formula-generated (IF(AND) formula). As a result, it might be outputting values as text rather than numbers, even though they appear as numbers visually. Additionally, the fact that the index values are left-aligned suggests the values are not numbers but text, like '1, '2 (with an implicit apostrophe ' prefix, which is not shown in the sheet). This discrepancy affects the INDEX(COLLECT) function because data type mismatches between the {range} (criteria range) and the value (criteria value) can prevent proper matching.

Observation About the `COLLECT` Function:

In the COLLECT function's criteria combination ({range}, value):

Data Type Matters for the {range}:
- If the {range} contains text-formatted numbers (e.g., '1 as text) and the value is a number (e.g., 1), the COLLECT function will not match.
- To fix this, the {range} must be explicitly converted to a number using the VALUE function.
Flexibility for value (Criteria):
- The COLLECT function seems more forgiving with the value (criteria). For example:
  - If the value is a text-formatted number (e.g., '1 as text) and the {range} contains actual numbers, the COLLECT function still evaluates as True and matches correctly.
- This means the key fix is ensuring the {range} has the correct data type, even if the value (criteria) is formatted as text.

Solution to Fix the Data Type in the Source Sheet:

The issue can be resolved by converting the HV Breaker Supplier INDEX {range} in the source sheet explicitly to numbers. The updated formula below demonstrates how to use VALUE within the COLLECT function:

=IFERROR(

    INDEX(

        COLLECT(

            {source sheet : Leadtime}, 

            {source sheet : Supplier}, [HV Breaker Supplier]@row, 

            {source sheet : Supplier INDEX}, VALUE(@cell) = [HV Breaker Supplier INDEX]@row

        ), 

        1

    ), 

    ""

)

Breakdown of the Fix:

{source sheet : Supplier INDEX} (Range):
- If this range contains text-formatted numbers, applying the VALUE function ensures they are treated as numbers.
Criteria Matching:
- Even if [HV Breaker Supplier INDEX]@row in the target sheet is text, the COLLECT function still matches it correctly against the numeric {range}.
Consistency:
- This ensures the {range} in the source sheet is always in a consistent format (number), resolving potential type mismatch issues.

Why This Works:

The VALUE function explicitly converts text-formatted numbers in the {range} to actual numbers. Since the COLLECT function is more forgiving with the value (criteria), the formula now evaluates correctly and returns matching results.

Practical Takeaways:

Left-aligned numbers in Smartsheet are a red flag for text-formatted data.
Always ensure numeric consistency in your {range} when using COLLECT.
If the {range} is derived from a formula, it’s safer to wrap it in VALUE() or similar functions to enforce the correct type.
The COLLECT function is more flexible with the value (criteria), so focus on fixing the {range}.

By applying the formula with VALUE as shown above, the INDEX(COLLECT) function should work even if the source sheet's column is generated via a formula, resolving the data type mismatch caused by text-formatted numbers.

Source Sheet INDEX Value Type, Text or Number

Target Sheet New Formula (Use the VALUE function)

jmyzk_cloudsmart_jp · 11/10/24

Hi @RotFraught

You can use the COLLECT function to create a range of lead time data that matches your criteria.

=IFERROR(INDEX(COLLECT({source sheet : Leadtime}, {source sheet : Supplier}, [HV Breaker Supplier]@row, {source sheet : Supplier INDEX}, [HV Breaker Supplier INDEX]@row), 1), "")

https://app.smartsheet.com/b/publish?EQBCT=883a234140864c5ca5222f8a4a010704

BTY, your question is a model example with clear explanations, real-world examples, and visuals, making it easy to understand and answer. It sets an excellent standard for asking structured, helpful questions.

RotFraught · 11/10/24

Thanks @jmyzk_cloudsmart_jp for response and always try to provide as much context as possible.

I did try the formula but have something wrong, detailing how I did the formula in images and the formula below and possibly you can help point me to the error I have (either in the formula or in the way the Source and Target sheets are constructed:

After the formula provided as formatted - it would bring back a NULL result (" ") even though the Supplier Name and HV Breaker Supplier INDEX matches had a Leadtime - DAYS data point to pull in from a Match. I tested this by putting "NO MATCH" between the " " and it brought back NO MATCH.

Formula:

=IFERROR(INDEX(COLLECT({Equipment Leadtime Range 3}, {Equipment Leadtime Range 7}, [HV Breaker Supplier]@row, {Equipment Leadtime Range 8}, [HV Breaker Supplier INDEX]@row), 1), "")

Source Sheet | Range References:

1.{Equipment Leadtime Range 3} - the column that is the Leadtime data to bring in

NOTE - this column itself is a formula that takes the # of week for each row input by suppliers and converts it to DAYS

2. {Equipment Leadtime Range 7} - the column that is the Supplier Name that would match the "HV Breaker Supplier" Column in the Target Sheet.

NOTE - on the Target Sheet the "HV Breaker Supplier" Column is a drop down list that Data Shuttle creates from the Source Sheet incase that matters

NOTE - this column itself is just a Text Field

3. {Equipment Leadtime Range 8} - the column that is the "HV Breaker Supplier INDEX" number that would match with the Target Sheet column of the same name.

NOTE: On both the Target and Source sheets - this column is a IF(AND formula that assigns a index code to a range of KV ratings from the Breakers

Below is what the Target Sheet w/ Formula looks like.

Paul Newcome · 11/11/24

If you remove the IFERROR from the formula, what exactly do you get?

=INDEX(COLLECT(……………..), 1)

RotFraught · 11/11/24

@Paul Newcome - I got the same result. As it turned out I tried hard coding the "HV Breaker Supplier INDEX in the Source sheet as a trial and error - possibly the source reference wouldn't work if the column had a formula in it. When I replaced the formula there (which was a "IF(AND" formula) the INDEX(COLLECT formula worked even with the IFERROR.

Curious as the same field on the Target sheet uses a similar formula to assign a 1 thru 5 INDEX number but this resolved the formula.

…is this a known behavior for INDEX(COLLECT? Would be nice to use that multiple match function even when Source sheets use formulas - but for now this is resolved. Thanks all!

Source Sheet with formula - replaced formula with the #

jmyzk_cloudsmart_jp · 11/12/24

@RotFraught

The last comment suggests the issue lies in the data type of the HV Breaker Supplier INDEX column in the Source Sheet, which likely caused the INDEX(COLLECT) function to fail. Here's how the data type mismatch can explain and resolve the issue: