| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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-- |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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-- |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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-- |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookup Values in a list
Yes! This works great! Thank you! Thank you!
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup a list of values for an array formula | Excel Worksheet Functions | |||
| Lookup a value in a list & return multiple corresponding values | Excel Worksheet Functions | |||
| Returning multiple corresponding values using lookup in a list | Excel Discussion (Misc queries) | |||
| Lookup Max and Min Values in a List | Excel Discussion (Misc queries) | |||
| Lookup values in a list and return multiple rows of data | Excel Worksheet Functions | |||
Hybrid Mode
