return Index Match Match value

[Robidick]

Hi,

My first thread in the Excel masters world, hope I do a good job

I have an overview of a multitude of products with their product details listed in a table form.
To easily first the information regarding the product I'm looking for I've used the Index Match Match function (in a new "looker" sheet) with the index being the full product table, row the product code and column the type of information.

That would be for the underneath table:
=INDEX(sheet1!$A$2:$H$9;MATCH(A1;sheet1!$B:$B;0);MATCH(B$1;sheet1!$2:$2;0))

The problem is that the products get updated, receive a new "material" number and get added to the list. The only way to know what the current correct version is,is by the "Status Code" being "S".

QUESTION:
How can I incorporate in my INDEXMATCHMATCH that it only returns the information of products that have status code "S" and thus ignoring the other, older, versions.
Products can have a different range of versions (going from 1 to 6) so a count or (iferror row+1) or stuff like that would not work 100% bulletproof.

Material	product code	Description	Status Code	Count	Height	Length	Depth
P1234501	12345​	big ol' bush	A1	50​	40,000​	20​	1​
P1234502	12345​	big ol' bush	A2	50​	41,000​	20​	1​
P1234503	12345​	big ol' bush	S	50​	41,0002​	20​	1​
P5432101	54321​	very small bush	S	100​	10,000​	10​	0,5​
P5432102	54321​	very small bush	A2	100​	10,900​	10​	0,5​
P4567801	45678​	medium bush	A2	75​	30,000​	15​	0,75​
P4567802	45678​	medium bush	S	75​	40,000​	15​	0,75​
P4567803	45678​	medium bush	A1	75​	32,000​	15​	0,75​

Hope this makes sense and you guys can help me out.

Feel free to ask more questions if its not clear.
Thanks for the help,
Robin

 

[steve the fish]

Heres a couple of different ways:

=INDEX($A$2:$H$9,MATCH(1,INDEX((($A$2:$A$9=$J$2)*($D$2:$D$9="S")),0),0),MATCH($J$3,$A$1:$H$1,0))
=LOOKUP(2,1/(($A$2:$A$9=$J$2)*($D$2:$D$9="S")),INDEX($A$2:$H$9,,MATCH($J$3,$A$1:$H$1,0)))

where your material code is in J2 and the header to return is in J3.

 

[pjmorris]

or try this array formula (press CTRL-SHFT-ENTER to enter the formula):

=INDEX(sheet1!$A$2:$H$9;MATCH(A1 & "S"; (sheet1!$B:$B & sheet1!$D:$D);0);MATCH(B$1;sheet1!$2:$2;0))

if you've entered it correctly it should have { } braces around it, they can't just be typed in which is why they're not show above

I think I'd be inclined to replace your column references with named ranges that automatically adjust to the number of entries.

HTH

 

[Robidick]

Hi all,

Thank you very much for your suggestions.
Although they all do work, they do not seem bulletproof.

=INDEX($A$2:$H$9,MATCH(1,INDEX((($A$2:$A$9=$J$2)*($D$2:$D$9="S")),0),0),MATCH($J$3,$A$1:$H$1,0))
Gives the correct values but does not seem to eliminate based on the correct status code

=LOOKUP(2,1/(($A$2:$A$9=$J$2)*($D$2:$D$9="S")),INDEX($A$2:$H$9,,MATCH($J$3,$A$1:$H$1,0)))
Does eliminate based on the status code but does not return the correct value anymore in the other categories

=INDEX(sheet1!$A$2:$H$9;MATCH(A1 & "S"; (sheet1!$B:$B & sheet1!$D:$D);0);MATCH(B$1;sheet1!$2:$2;0))
Does not eliminate based on the correct status code ;(

Afterwhich I decided to follow your last tip, pjmorris, to transform my index data to a table to have my columns as named ranges and this did the trick!!
=INDEX(sheet1!$A$2:$H$9;MATCH(A1 & "S"; (sheet1!$B:$B & sheet1!$D:$D);0);MATCH(B$1;sheet1!$2:$2;0))
Is the winner! This is also a code I'm partly familiar with but have not yet 100% mastered it to implement it by myself.

Craziest part is that this also avoids the extra formulas I had to nest to make the "not stored as numbers" problem work. The index data has all its product codes stored as text while, when you manually write the code it gets stored as a number which ****ed up the formula.

Graçias muchos spaghetti

 

[pjmorris]

glad I was able to help.