• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match/ Lookup for multiple lines

cmf735

Member
I'm trying to figure out how to look up multiple different lines based off of a single value. So I've attached a copy of my main data. I'm trying pull the JO# into a different sheet based off the Current Status. So "In-Process Cell #1" has 3 JO#s and I want to pull all of those into a list in a different sheet, where In-Process Cell #2 and #3 only have one JO which I have no problem pulling, but Cell #1 I only get one JO to pull. This is the formula I'm using to find 1 JO so far =INDEX(owssvr!A:A, MATCH(Sheet2!E1, owssvr!G:G,0)). Idk if I need an IF or IFERROR or COUNTIF, like if equals Cell E3, which is the first and only JO that I'm pulling, count the next value that matches Cell #1? Any help is greatly appreciated!
JO#SO#ExpediteDock DateKanban OrderCurrent StatusRequestedProduction DueShip DatePart NumberQTY
TS-005027-AKANBAN
FALSE​
12/25/2026​
TRUE​
In-Process Cell #1
12/25/2026​
3/20/2021​
12/25/2026​
MK-029
5​
TS-004441-C1005605
TRUE​
4/5/2021​
FALSE​
In-Process Cell #1
4/5/2021​
3/30/2021​
4/2/2021​
TS-242
10​
TS-004837-B1006124
TRUE​
12/25/2026​
FALSE​
In-Process Cell #1
12/25/2026​
3/31/2021​
12/25/2026​
TS-211
9​
MM-003403-A1004180
TRUE​
12/25/2026​
FALSE​
In-Process Cell #2
12/25/2026​
4/5/2021​
12/25/2026​
AA-110
20​
TS-004860-21KANBAN
TRUE​
12/25/2026​
TRUE​
In-Process Cell #3
12/25/2026​
4/23/2021​
12/25/2026​
TS-294
28​
 
cmf735
#1 You seems to duplicate You thread .. or not as You wrote?
#2 You should use code - tags with Your formulas
#3 Have You think - how other can test Your given ... data? Upload a sample Excel-file
 
Here is a spreadsheet sample of the data instead of the copy and paste I did earlier. You can see that it only returns one value for each stage but I'm trying to pull all of the values that match the criteria. The first two stages will have much more than the other stages that's why I dragged those down further. Ignore the row 2 numbers.
 

Attachments

  • In Process.xlsx
    21.9 KB · Views: 3
C3 of the Stages sheet:
=FILTER(Data!$A2:$A74,C1=Data!$F2:$F74,"None")
copy across.
If your data on the Data sheet goes beyond row 74 then change those numbers in the formula, but better still, change the data on the Data sheet to a proper Excel Table and use the formula:
=FILTER(Table1[JO'#],C1=Table1[Current Status],"None")
but that won't copy across well so use:
=FILTER(Table1[[JO'#]:[JO'#]],C1=Table1[[Current Status]:[Current Status]],"None")
and you won't have to worry about how many rows are involved on the Data sheet.
 
Last edited:
C3 of the Satges sheet:
=FILTER(Data!$A2:$A74,C1=Data!$F2:$F74,"None")
copy across.
If your data on the Data sheet goes beyond row 74 then change those numbers in the formula, but better still, change the data on the Data sheet to a proper Excel Table and use the formula:
=FILTER(Table1[JO'#],C1=Table1[Current Status],"None")
but that won't copy across well so use:
=FILTER(Table1[[JO'#]:[JO'#]],C1=Table1[[Current Status]:[Current Status]],"None")

I'm getting a #SPILL! error.
 
Very probably, but I don't where those values are, nor do I know where you want the results.
Attach your updated file, it will also tell me which of the formula from msg#4 you ended up using.

Essentially, we'll be doing something like:
=FILTER(Data!$A2:$A74,((D1=Data!$F2:$F74)+(E1=Data!$F2:$F74)>0),"None")
or:
=FILTER(Data!$A2:$A74,(D1=Data!$F2:$F74)+(E1=Data!$F2:$F74),"None")
where this is including both Production ready and In-Process Cell #1
 
Last edited:
Sorry attached is the spreadsheet. Should've attached that Friday. I'm using Index/Match to fill in Part number and QTY, I'm not sure why my information isn't pulling. I have to figure that out. Anyways, under "Inspection" is what I'm trying to pull the JO#s containing "Waiting for Final Inspection" and "In-Process Final Inspection". I tried to use "RIGHT" to only count the word "Inspection". Thought that might have been a way to get it to work.
 

Attachments

  • In Process.xlsx
    462.8 KB · Views: 3
It's part of the INDEX function, the array of columns: columns 1,2,3,4 of the first argument of the INDEX function.
 
Back
Top