I think I need an index match formula as source sheet is unsorted

Terri-Lynn Morrison

Hi, people smarter than me. I have been trying for days and days to get a formula to work. The source data is not sorted; it is populated by form data.

The vloopup formula only duplicated the same result from the first row over again.
=IF(VLOOKUP([Primary Column]@row, {2024 Circulation File Num to Support Level}, 1, false) = [Primary Column]@row, VLOOKUP("AH&H", {Department to Support Level}, 4, false), "No Response") So I added a helper column, but that did not change anything using Vlookup.

So, I tried switching to both Index Collect and Index Match. I even created a helper column in my source sheet and the current sheet in which I want the data inputted. Index Collect just had an unparsable error. Index Match, only returns a result when the Helper column is blank in the current sheet I want data imported into, and the result is from the first row of the helper column in the source sheet.

=INDEX(MATCH({2024 Support Level}, {2024 Helper CS File & Responder}, [Helper AH&H]@row,1) I also tried changing the ",1)" to ",0", but no change.

{2024 Support Level} is a drop-down field in the source sheet. I would prefer to have the symbol column returned which is {2024 Support Symbols} using the stop lights, but at this point, I will take anything. {2024 Helper CS File & Responder} is the source sheet helper which contains a unique ID of file number and company name, and matches the data in the Helper Column, on my current sheet.

As you can see in the Current Sheet Screenshot, the only time it did not error, was when the Helper AH&H column had a blank cell, and the returned result was from the first row in the source sheet. If there is #NO MATCH I would prefer it to be blank or say "No Response".

Kelly Moore

Hey @Terri-Lynn Morrison

Happy to help. Before building an Index/Match using your helper columns, would you like to try the formula on your original dataset? You might not need the helpers? If you show a screenshot with the original columns I can provide more explicit instruction - if needed.

The Index Match can use single columns as your data ranges, vs the entire table that is needed in VLOOKUP. This makes the formula more versatile and robust. The INDEX/COLLECT is even more versatile as it can accomodate more than one criteria.

The Index Match has this format:

=INDEX({source sheet data you want collected},MATCH(match@row, {source sheet column where match is found},0))

To incorporate both the CS numbers and the AH&H you would use an Index/Collect. You would have to build the cross references manually. Each reference is a single column

=INDEX(COLLECT({2024 Support Symbols},{source sheet Location column where AH H is found}, "AH&H",{CS File column}, [Primary column]@row),1)

Would this work for you?

Kelly

Terri-Lynn Morrison

Hi Kelly,

I have redacted the data in my source sheet screenshot, but I know what they are. Some columns are not displayed but are unnecessary for this purpose. What I need to compare the current sheet to is CS File Number (not sorted) & Department or Company Responding (not sorted). If both are present in the same row, then return the value of Support Level (or Support Symbol), otherwise blank or "No Response". In the current sheet screenshot above, the current sheet has header rows for all the Department or Company Responding options. There are 26 different Department or Company names/columns. The goal is to have a grid that shows who responded to each request as a quick reference, as I can't control who responds to what request at any given time. There will be empty cells like Row 1 in the source sheet. I am done work for today, but will check back tomorrow if you have something else for me to try. If you can be specific in the fields I need and where I would appreciate it. Life is a struggle right now, and my brain hurts on a good day, which this is not.

Kelly Moore

@Terri-Lynn Morrison

Remember that you will need to create the cross sheet references manually. You cannot simply copy paste this formula.

It's my understanding that you want to collect the Symbols from your source sheet when you have both matching CS file numbers and dept = AH&H. In the source sheet, each referenced range is a column. The number one at the end is part of the INDEX function.

=INDEX(COLLECT({2024 Support Symbols},{Copy of Redacted 2024 Dept or Company Responding}, "AH&H",{Copy of Redacted 2024 CS File Number}, [Primary column]@row),1)

if you also have the dept information in your Target sheet you can make your formula more dynamic by also adding that field

=INDEX(COLLECT({2024 Support Symbols},{Copy of Redacted 2024 Dept or Company Responding}, [your dept field]@row, {Copy of Redacted 2024 CS File Number}, [Primary column]@row),1)

Before adding an IFERROR to account for a NoMatch, we first always ensure that the formula works as expected.

Does this work for you?
Kelly

Terri-Lynn Morrison

Hi Kelly,

After some trial and error, it started working after I entered the formula in row 6 (which I do not remember doing) and got the result I wanted.

That formula pasted into Row 2 brought in a Yes (Green Check) response. Woot!

=INDEX(COLLECT({2024 Support Symbols}, {Copy of Redacted 2024 Dept or Company Responding}, "AH&H", {Copy of Redacted 2024 CS File Number}, [Primary Column]@row), 1)

I did add the company names to Row 1, if you think that would make referencing a cell easier than typing in the company name in each formula.