Index Match Match returning completely wrong values

Chris Martin

Hi Team

this is the formula

=INDEX({Sales and Transfer Calculation _sold}, MATCH(SKU@row, {Sales and Transfer Calculation_SKU}, MATCH([Closing Date Range]@row, {Sales and Transfer Calculation _Date}, 0)))

its returning values but not the right ones.

Any ideas?

thanks in advance

Chris

Andrée Starå

Hi @Chris Martin

I hope you're well and safe!

The structure for INDEX/MATCH looks like this. (you got two MATCH sections)

=
INDEX({ColumnWithTheValueYouWantToShow}; 
MATCH(CellThatHaveTheValueToMatch@row; {ColumnWithTheValueToMatchAgainsTheCell}; 0))

Depending on your country/region, you'll need to exchange the comma for a period and the semi-colon for a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Chris Martin

HI @Andrée Starå

Thanks for that. Correct me if im wrong, but im pretty sure you can have Index match match?

How do you have to set of criteria then? Eg i need to match to a SKU code and also a date

Cheers

Chris

Chris Martin

here is a youtube clip showing someone else doing it? https://www.youtube.com/watch?v=1-GDf3_gzTQ

i just cant figure why im getting wrong data.

Andrée Starå

@Chris Martin

Happy to help!

Yes, you're correct. You can have an INDEX MATCH MATCH.

What's the answer you're getting?

Chris Martin

Hi Andree

This is the source data, i have placed dummy data in to trail my idea before I go exporting it from software.

This is what is comes back with. QTy Sold out is the colum that the formulas is place in. Youll see that some of the data is correct and some is just wrong

Chris Martin

IT seems that the No match that shoudl be coming back is not. Some of the products are no listed in the source sheet. I need these to return a no match error so i can add a "iferror" function

Andrée Starå

@Chris Martin

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Chris Martin

Hi Cant share sheets but this is the process.

The forumla is intended to pull the sales figure from the source sheet if the they match the sku and closing date.

Does that make sense

Chris

Chris Martin

Any ideas @Andrée Starå

Paul H

@Chris Martin

I haven't had much luck with the double match, you could just put a helper column on each sheet that combines Closing date and SKU, that way it becomes a single match.