| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup multiple value
Is there any way to lookup multiple answers to a lookup formula.
Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup multiple value
With
Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookup multiple answers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup multiple value
DRAT! My fingers not typing what I'm thinking!
Formula 1 should be: E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A $5)+($A$2:$A$5<$D$1)*10^99,0),ROWS($E$1:$E1))),"" ) Formula 2 (the array formula) should be: E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)), ROWS($E$1:$E1))),"") Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookup multiple answers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup multiple value
On Apr 5, 9:06 pm, Ron Coderre
wrote: DRAT! My fingers not typing what I'm thinking! Formula 1 should be: E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A $5)+($A$2:$A$5<$D$1)*10^99,0),ROWS($E$1:$E1))),"" ) Formula 2 (the array formula) should be: E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)), ROWS($E$1:$E1))),"") Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Pls kindly sugess other formula is not properly |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup multiple value
On Apr 5, 9:02 pm, Ron Coderre
wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup multiple value
It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup multiple value
On Apr 6, 4:48 pm, Ron Coderre
wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup multiple value
Hi Ron,
your are not able to undersatand what i was say, with ur permission shall i send excel sheet , that given clear structure. On Apr 6, 5:48 pm, "deen" wrote: On Apr 6, 4:48 pm, Ron Coderre wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup multiple value
Assuming that A2:B5 contains the data, try the following...
Let D1 contains the part number of interest, such as 123 E2: =COUNTIF(A2:A5,D2) F2, copied down: =IF(ROWS(F$2:F2)<=$E$2,INDEX($B$2:$B$5,SMALL(IF($A $2:$A$5=$D$2,ROW($A$2:$ A$5)-ROW($A$2)+1),ROWS(F$2:F2))),"") Hope this helps! In article .com, "deen" wrote: Is there any way to lookup multiple answers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup for multiple criteria, multiple worksheets | Excel Worksheet Functions | |||
| Multiple Ifs, can't use vlookup | Excel Discussion (Misc queries) | |||
| How do I use VLOOKUP to ref multiple workbooks with multiple tabs? | Excel Discussion (Misc queries) | |||
| Multiple VLOOKUP | Excel Worksheet Functions | |||
| multiple vlookup | Excel Worksheet Functions | |||
Hybrid Mode
