Jun 01 2017
09:14 AM
- last edited on
Jul 25 2018
09:40 AM
by
TechCommunityAP
Jun 01 2017
09:14 AM
- last edited on
Jul 25 2018
09:40 AM
by
TechCommunityAP
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.
PartNum | Size | Colors | Sides | Ink1 | Ink2 | Ink3 | Ink4 | PartRev | Operation | Op Seq | Standard | Standard Type | Actual | CasePack | Resource Grp | Resource ID | ProdOrSetup | Basis | Color | SideSeam |
00100 | 4 1/4 x 2 3/8 x 8 3/16 | A | MAKEBAGS | 10 | 8.00 | PH | 8.35 | 1000 | 403 | 403 | B | 35 | White | No | ||||||
00101 | 4 1/4 x 2 3/8 x 8 3/16 | B | MAKEBAGS | 10 | 6.00 | PH | 8.13 | 1000 | 403 | 403 | B | 35# | Recycled Natural | No | ||||||
00102 | 10 x 13 | A | MAKEBAGS | 10 | 20.00 | PH | 22.36 | 500 | 103 | 103 | B | 30# | White | No |
Jun 01 2017 10:02 AM
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.
Jun 01 2017 10:05 AM
SolutionHi 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
Jun 01 2017 10:23 AM
That worked, Thank you
Jun 01 2017 10:24 AM
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.
Jun 01 2017 10:31 AM
This formula worked as well, Thanks
Jun 01 2017 10:50 AM
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.
Jun 01 2017 10:05 AM
SolutionHi 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