Index/match multiple criteria not working - HELP please

[dmj120]

I am trying to extract pricing based on an ID# and device code. I've tried a couple variations of what I read online, to no avail.

INDEX('Sheet A'!$F$2:$K$36310,MATCH($A4,'Sheet A'!$A$2:$A$36310, 0),3) works, but when I add the second 'row criteria' it all goes south.... example below

INDEX('Sheet A'!$F$2:$K$36310, MATCH(1,('Sheet A'!$A$2:$A$36310=$A4) * ('Sheet A'!$B$2:$B$36310=$E$1), 0) ,3)


Sheet A (original data)

New sheet

 

[dmj120]

Column B code and G1 were different formats.... one text and the other general

 

[NickFru]

Hello - not sure if this will help but you can try this out. Firstly, I would do a countifs to get unique count and lay it out like this;

Model ID. Code. Count
Result of count

From the original data;

=COUNTIFS(Original Data $A$2:$A$36310, Selected Model ID No, Original Data $B$2:$B$36310, Selected Code)

You can use Data validation on both Original data tab to get Model ID & Code respectively as part of the above COUNTIFS formula.

Once done, them run the following on the new sheet in cell A4 and copy across;

{=IF(ROWS(A$4:A4)>COUNT, RESULT,"",INDEX(Original Data A$2:A$36310, SMALL(IF(Original Data $A$2:$A$36310=Model ID Selection, IF(Original Data $B$36310=Code Selection, ROW(Original Data $A$2:$A$36310)-ROW(Original Data $A$2)+1)), ROWS($A$4:A4)} and copy this across the columns;

Model ID. Code Mfg. Device. Mdl. Pricing

You will need to insert a pricing on the original data in order to pull over into the new data starting as mentioned in cell A4.

As you start to populate the original data list with more lines, this will update on the new tab when you start to add more data.

Hope this helps

 

[NickFru]

Hello - just thinking, you could also try this with FILTER;

So in cell A4 on new data;

=FILTER(Original Data $A$2:$A$36310, (Original Data $A$2:$A$A36310=Model ID)*(Original Data $B$2:$B$36310=Code)

 

[dmj120]

Hello - just thinking, you could also try this with FILTER;

So in cell A4 on new data;

=FILTER(Original Data $A$2:$A$36310, (Original Data $A$2:$A$A36310=Model ID)*(Original Data $B$2:$B$36310=Code)

Didn't think about filter. Thanks for the input!

 

[NickFru]

Ah no problem at all, glad to try and be of help if I can.