SOLVED

I am trying to return a value based on 2 conditions that are in columns.

Copper Contributor

I am trying to return a value based on 2 diferent values. I have tried index-match-match, vlookup-match, but can only get one of the conditions to show. I want to look at partnum as condition1, operation as condition 2 and return an value for standard.

PartNumSizeColorsSidesInk1Ink2Ink3Ink4PartRevOperationOp SeqStandardStandard TypeActual CasePackResource GrpResource IDProdOrSetupBasisColorSideSeam
001004 1/4 x 2 3/8 x 8 3/16     AMAKEBAGS108.00PH8.351000403403B35WhiteNo
001014 1/4 x 2 3/8 x 8 3/16     BMAKEBAGS106.00PH8.131000403403B35#Recycled NaturalNo
0010210 x 13      AMAKEBAGS1020.00PH22.36500103103B30#WhiteNo
6 Replies

Hello Scott

 

=LOOKUP(PI(),1/(tbl_Original[PartNum]=[PartNum])/(tbl_Original[Operation]=[Operation]),tbl_Original[Standard])

tbl_Original is the orgininal data.

tbl_Extract is the table with the two conditions.

 

 

best response confirmed by Scott Hetzel (Copper Contributor)
Solution

Hi Scott,

 

Your text is practically unreadable. However,

 

How to use INDEX/MATCH with multiply criterias is, for example, here https://exceljet.net/formula/index-and-match-with-multiple-criteria. The idea is to use array (Ctrl+Shift+Enter) formula with checking multiplication (actually logical 'and') of criterias within MATCH.

 

Something like

=INDEX(<standard column>,
   MATCH(1,
      (<PN>=<part numbers column>)*(<OPR>=<operations column>)
   ,0),0
)

assuming you have PartN and operations values in each row

Sergej, I use Table2Clipboard as a Firefox addon.

Just right-klick inside a table and copy the table, switch to Excel and paste the table. Works fine.

 

This formula worked as well, Thanks

Detlef, thank you - I even didn't catch that was copy/paste of the table into the text.

I'm mainly on Vivaldi and have no Firefox at all, but simple back copy/paste from Vivaldi to Excel returned correct table. At least for this case.

1 best response

Accepted Solutions
best response confirmed by Scott Hetzel (Copper Contributor)
Solution

Hi Scott,

 

Your text is practically unreadable. However,

 

How to use INDEX/MATCH with multiply criterias is, for example, here https://exceljet.net/formula/index-and-match-with-multiple-criteria. The idea is to use array (Ctrl+Shift+Enter) formula with checking multiplication (actually logical 'and') of criterias within MATCH.

 

Something like

=INDEX(<standard column>,
   MATCH(1,
      (<PN>=<part numbers column>)*(<OPR>=<operations column>)
   ,0),0
)

assuming you have PartN and operations values in each row

View solution in original post