Lookup Values in a list
 

I have two tables. One has a list of names, the other has a list of order
numbers with Customer IDs (we'll call them names for the example). IF I enter
a Customer ID (name) in column A, then column B should show the closed orders
for that customer along with the order number.

cola colb colc
Name1 Order#1 Closed
Name1 Order#2 Closed
Name2 Order#3 Open
Name2 Order#1 Closed
Name4 Order#1 Open

Result would be in ColB on another sheet when I type in the Customer ID in
column A as follows
ColA Colb
Name1 Order#1, Order#2
Name2 Order#3
Name3 --customer not found--
Name4 --no closed orders--

Lookup Values in a list
 

Assuming your data A1:C5 in sheet1

In sheet2
A1: Name1
A2: Name2
A3: Name3
A4: Name4

B1:
=IF(ISERR(SMALL(IF((Sheet1!$A$1:$A$5=Sheet2!$A1)*( Sheet1!$C$1:$C$5="Closed"),ROW(INDIRECT("1:"&ROWS( Sheet1!$C$1:$C$5)))),COLUMNS($A:A))),"",INDEX(Shee t1!$B$1:$B$5,SMALL(IF((Sheet1!$A$1:$A$5=Sheet2!$A1 )*(Sheet1!$C$1:$C$5="Closed"),ROW(INDIRECT("1:"&RO WS(Sheet1!$C$1:$C$5)))),COLUMNS($A:A))))

ctrl+shift+enter, not just enter
Copy across and down



=IF(ISERR(SMALL(IF((Sheet1!$A$1:$A$5=Sheet2!$A1)*( Sheet1!$C$1:$C$5="Closed"),ROW(INDIRECT("1:"&ROWS( Sheet1!$C$1:$C$5)))),COLUMNS($A:A))),"",INDEX(Shee t1!$B$1:$B$5,SMALL(IF((Sheet1!$A$1:$A$5=Sheet2!$A1 )*(Sheet1!$C$1:$C$5="Closed"),ROW(INDIRECT("1:"&RO WS(Sheet1!$C$1:$C$5)))),COLUMNS($A:A))))

"notso" wrote:

I have two tables. One has a list of names, the other has a list of order
numbers with Customer IDs (we'll call them names for the example). IF I enter
a Customer ID (name) in column A, then column B should show the closed orders
for that customer along with the order number.

cola colb colc
Name1 Order#1 Closed
Name1 Order#2 Closed
Name2 Order#3 Open
Name2 Order#1 Closed
Name4 Order#1 Open

Result would be in ColB on another sheet when I type in the Customer ID in
column A as follows
ColA Colb
Name1 Order#1, Order#2
Name2 Order#3
Name3 --customer not found--
Name4 --no closed orders--

Lookup Values in a list
 

Ignore the last formula. It's duplicate entry


"Teethless mama" wrote:

Assuming your data A1:C5 in sheet1

In sheet2
A1: Name1
A2: Name2
A3: Name3
A4: Name4

B1:
=IF(ISERR(SMALL(IF((Sheet1!$A$1:$A$5=Sheet2!$A1)*( Sheet1!$C$1:$C$5="Closed"),ROW(INDIRECT("1:"&ROWS( Sheet1!$C$1:$C$5)))),COLUMNS($A:A))),"",INDEX(Shee t1!$B$1:$B$5,SMALL(IF((Sheet1!$A$1:$A$5=Sheet2!$A1 )*(Sheet1!$C$1:$C$5="Closed"),ROW(INDIRECT("1:"&RO WS(Sheet1!$C$1:$C$5)))),COLUMNS($A:A))))

ctrl+shift+enter, not just enter
Copy across and down



=IF(ISERR(SMALL(IF((Sheet1!$A$1:$A$5=Sheet2!$A1)*( Sheet1!$C$1:$C$5="Closed"),ROW(INDIRECT("1:"&ROWS( Sheet1!$C$1:$C$5)))),COLUMNS($A:A))),"",INDEX(Shee t1!$B$1:$B$5,SMALL(IF((Sheet1!$A$1:$A$5=Sheet2!$A1 )*(Sheet1!$C$1:$C$5="Closed"),ROW(INDIRECT("1:"&RO WS(Sheet1!$C$1:$C$5)))),COLUMNS($A:A))))

"notso" wrote:

I have two tables. One has a list of names, the other has a list of order
numbers with Customer IDs (we'll call them names for the example). IF I enter
a Customer ID (name) in column A, then column B should show the closed orders
for that customer along with the order number.

cola colb colc
Name1 Order#1 Closed
Name1 Order#2 Closed
Name2 Order#3 Open
Name2 Order#1 Closed
Name4 Order#1 Open

Result would be in ColB on another sheet when I type in the Customer ID in
column A as follows
ColA Colb
Name1 Order#1, Order#2
Name2 Order#3
Name3 --customer not found--
Name4 --no closed orders--

Lookup Values in a list
 

Yes! This works great! Thank you! Thank you!