Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can VLookup function find and list multiple records?
Dear Experts,
I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#2
|
|||
|
|||
Hi!
The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#3
|
|||
|
|||
Dear Biff, thank you for your prompt response.
I had tried your method but it won't show the second value of Bk-Alpha which reads at 99 and 95. Is there any way I can use to display all values of column 3? ie; show both 99 and 95 for bk-alpha when chosen. Thank you once again. B/Regards, Richmond "Biff" wrote: Hi! The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#4
|
|||
|
|||
Post the *EXACT* formula that you used.
Also, this is an array formula which means that after you type the formula instead of just hitting ENTER you *MUST* hold down the CTRL and the SHIFT keys, then hit ENTER. If done properly Excel will place squiggly braces { } around the formula. You must use the key combo of CTRL,SHIFT,ENTER to accomplish this. You cannot just type them in. Biff "Rich - SG" wrote in message ... Dear Biff, thank you for your prompt response. I had tried your method but it won't show the second value of Bk-Alpha which reads at 99 and 95. Is there any way I can use to display all values of column 3? ie; show both 99 and 95 for bk-alpha when chosen. Thank you once again. B/Regards, Richmond "Biff" wrote: Hi! The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#5
|
|||
|
|||
Dear Biff,
Can you elaborate more? I had already done it correctly and the formula did show the braces before and after the formula. If possible, can you give me a more detailed illustration how to go about? Thank you, Rich "Biff" wrote: Post the *EXACT* formula that you used. Also, this is an array formula which means that after you type the formula instead of just hitting ENTER you *MUST* hold down the CTRL and the SHIFT keys, then hit ENTER. If done properly Excel will place squiggly braces { } around the formula. You must use the key combo of CTRL,SHIFT,ENTER to accomplish this. You cannot just type them in. Biff "Rich - SG" wrote in message ... Dear Biff, thank you for your prompt response. I had tried your method but it won't show the second value of Bk-Alpha which reads at 99 and 95. Is there any way I can use to display all values of column 3? ie; show both 99 and 95 for bk-alpha when chosen. Thank you once again. B/Regards, Richmond "Biff" wrote: Hi! The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#6
|
|||
|
|||
Hi!
Do you want to see a sample file? If so, post an email address and I'll gladly send you one. Biff "Rich - SG" wrote in message ... Dear Biff, Can you elaborate more? I had already done it correctly and the formula did show the braces before and after the formula. If possible, can you give me a more detailed illustration how to go about? Thank you, Rich "Biff" wrote: Post the *EXACT* formula that you used. Also, this is an array formula which means that after you type the formula instead of just hitting ENTER you *MUST* hold down the CTRL and the SHIFT keys, then hit ENTER. If done properly Excel will place squiggly braces { } around the formula. You must use the key combo of CTRL,SHIFT,ENTER to accomplish this. You cannot just type them in. Biff "Rich - SG" wrote in message ... Dear Biff, thank you for your prompt response. I had tried your method but it won't show the second value of Bk-Alpha which reads at 99 and 95. Is there any way I can use to display all values of column 3? ie; show both 99 and 95 for bk-alpha when chosen. Thank you once again. B/Regards, Richmond "Biff" wrote: Hi! The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#7
|
|||
|
|||
Also, in your original post you have this table:
Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 BK-Alpha is only listed once! I assumed BK-Apha was just a typo. So, if you use that formula on the above table you'll only get a single row of data returned! Biff "Biff" wrote in message ... Hi! Do you want to see a sample file? If so, post an email address and I'll gladly send you one. Biff "Rich - SG" wrote in message ... Dear Biff, Can you elaborate more? I had already done it correctly and the formula did show the braces before and after the formula. If possible, can you give me a more detailed illustration how to go about? Thank you, Rich "Biff" wrote: Post the *EXACT* formula that you used. Also, this is an array formula which means that after you type the formula instead of just hitting ENTER you *MUST* hold down the CTRL and the SHIFT keys, then hit ENTER. If done properly Excel will place squiggly braces { } around the formula. You must use the key combo of CTRL,SHIFT,ENTER to accomplish this. You cannot just type them in. Biff "Rich - SG" wrote in message ... Dear Biff, thank you for your prompt response. I had tried your method but it won't show the second value of Bk-Alpha which reads at 99 and 95. Is there any way I can use to display all values of column 3? ie; show both 99 and 95 for bk-alpha when chosen. Thank you once again. B/Regards, Richmond "Biff" wrote: Hi! The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#9
|
|||
|
|||
Ok, sample file on it's way.
Biff "Rich - SG" wrote in message ... Yeah sure, thank you. My e-mail addy is . Btw, for the following example, this is what I am looking for... Supposing table of data being: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 and when I key Bk-Alpha, it will show 2 rows of data as per following format: Bk-Alpha ABC & Co 99.00 Bk-Alpha XYZ & Co 95.00 Alternatively, if it can be done using other function or others, kindly let me know. I'll use any available or feasible means. Thank you very much, Rich "Biff" wrote: Hi! Do you want to see a sample file? If so, post an email address and I'll gladly send you one. Biff "Rich - SG" wrote in message ... Dear Biff, Can you elaborate more? I had already done it correctly and the formula did show the braces before and after the formula. If possible, can you give me a more detailed illustration how to go about? Thank you, Rich "Biff" wrote: Post the *EXACT* formula that you used. Also, this is an array formula which means that after you type the formula instead of just hitting ENTER you *MUST* hold down the CTRL and the SHIFT keys, then hit ENTER. If done properly Excel will place squiggly braces { } around the formula. You must use the key combo of CTRL,SHIFT,ENTER to accomplish this. You cannot just type them in. Biff "Rich - SG" wrote in message ... Dear Biff, thank you for your prompt response. I had tried your method but it won't show the second value of Bk-Alpha which reads at 99 and 95. Is there any way I can use to display all values of column 3? ie; show both 99 and 95 for bk-alpha when chosen. Thank you once again. B/Regards, Richmond "Biff" wrote: Hi! The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#10
|
|||
|
|||
Hi Biff,
Sorry for the typo error. I had corrected the error and it worked. A million thanks and God Bless. Have a good day! Cheers, Rich "Rich - SG" wrote: Yeah sure, thank you. My e-mail addy is . Btw, for the following example, this is what I am looking for... Supposing table of data being: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 and when I key Bk-Alpha, it will show 2 rows of data as per following format: Bk-Alpha ABC & Co 99.00 Bk-Alpha XYZ & Co 95.00 Alternatively, if it can be done using other function or others, kindly let me know. I'll use any available or feasible means. Thank you very much, Rich "Biff" wrote: Hi! Do you want to see a sample file? If so, post an email address and I'll gladly send you one. Biff "Rich - SG" wrote in message ... Dear Biff, Can you elaborate more? I had already done it correctly and the formula did show the braces before and after the formula. If possible, can you give me a more detailed illustration how to go about? Thank you, Rich "Biff" wrote: Post the *EXACT* formula that you used. Also, this is an array formula which means that after you type the formula instead of just hitting ENTER you *MUST* hold down the CTRL and the SHIFT keys, then hit ENTER. If done properly Excel will place squiggly braces { } around the formula. You must use the key combo of CTRL,SHIFT,ENTER to accomplish this. You cannot just type them in. Biff "Rich - SG" wrote in message ... Dear Biff, thank you for your prompt response. I had tried your method but it won't show the second value of Bk-Alpha which reads at 99 and 95. Is there any way I can use to display all values of column 3? ie; show both 99 and 95 for bk-alpha when chosen. Thank you once again. B/Regards, Richmond "Biff" wrote: Hi! The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#11
|
|||
|
|||
Hi Biff,
Hope you are doing fine. Is there any way I can hide the #Num! sign when there are no data? I tried certain ways but still cannot. Hope to hear from you soon, Rich "Rich - SG" wrote: Hi Biff, Sorry for the typo error. I had corrected the error and it worked. A million thanks and God Bless. Have a good day! Cheers, Rich "Rich - SG" wrote: Yeah sure, thank you. My e-mail addy is . Btw, for the following example, this is what I am looking for... Supposing table of data being: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 and when I key Bk-Alpha, it will show 2 rows of data as per following format: Bk-Alpha ABC & Co 99.00 Bk-Alpha XYZ & Co 95.00 Alternatively, if it can be done using other function or others, kindly let me know. I'll use any available or feasible means. Thank you very much, Rich "Biff" wrote: Hi! Do you want to see a sample file? If so, post an email address and I'll gladly send you one. Biff "Rich - SG" wrote in message ... Dear Biff, Can you elaborate more? I had already done it correctly and the formula did show the braces before and after the formula. If possible, can you give me a more detailed illustration how to go about? Thank you, Rich "Biff" wrote: Post the *EXACT* formula that you used. Also, this is an array formula which means that after you type the formula instead of just hitting ENTER you *MUST* hold down the CTRL and the SHIFT keys, then hit ENTER. If done properly Excel will place squiggly braces { } around the formula. You must use the key combo of CTRL,SHIFT,ENTER to accomplish this. You cannot just type them in. Biff "Rich - SG" wrote in message ... Dear Biff, thank you for your prompt response. I had tried your method but it won't show the second value of Bk-Alpha which reads at 99 and 95. Is there any way I can use to display all values of column 3? ie; show both 99 and 95 for bk-alpha when chosen. Thank you once again. B/Regards, Richmond "Biff" wrote: Hi! The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
#12
|
|||
|
|||
Hi!
There are a couple of ways to do that. You can put an error trap in the formula. This makes the formula twice as long and may not be very efficient especially if your workbook is large and has a lot of formulas: =IF(ISERROR(SMALL(IF(Sheet1!$A$2:$A$4=$A$2,ROW($1: $3)),ROW(1:1))),"",INDEX(Sheet1!B$2:B$4,SMALL(IF(S heet1!$A$2:$A$4=$A$2,ROW($1:$3)),ROW(1:1)))) Another way is to use the shorter formula (without the error trap) and then use conditional formatting to hide the errors. for example, say the formulas were in a range from B2:C10. Select that range, B2:C10 Goto FormatConditional Formatting Select Formula Is In the box enter this formula: =ISERROR(B2) Click the Format button Set the font color to be the same as the background fill color Ok out Biff "Rich - SG" wrote in message ... Hi Biff, Hope you are doing fine. Is there any way I can hide the #Num! sign when there are no data? I tried certain ways but still cannot. Hope to hear from you soon, Rich "Rich - SG" wrote: Hi Biff, Sorry for the typo error. I had corrected the error and it worked. A million thanks and God Bless. Have a good day! Cheers, Rich "Rich - SG" wrote: Yeah sure, thank you. My e-mail addy is . Btw, for the following example, this is what I am looking for... Supposing table of data being: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 and when I key Bk-Alpha, it will show 2 rows of data as per following format: Bk-Alpha ABC & Co 99.00 Bk-Alpha XYZ & Co 95.00 Alternatively, if it can be done using other function or others, kindly let me know. I'll use any available or feasible means. Thank you very much, Rich "Biff" wrote: Hi! Do you want to see a sample file? If so, post an email address and I'll gladly send you one. Biff "Rich - SG" wrote in message ... Dear Biff, Can you elaborate more? I had already done it correctly and the formula did show the braces before and after the formula. If possible, can you give me a more detailed illustration how to go about? Thank you, Rich "Biff" wrote: Post the *EXACT* formula that you used. Also, this is an array formula which means that after you type the formula instead of just hitting ENTER you *MUST* hold down the CTRL and the SHIFT keys, then hit ENTER. If done properly Excel will place squiggly braces { } around the formula. You must use the key combo of CTRL,SHIFT,ENTER to accomplish this. You cannot just type them in. Biff "Rich - SG" wrote in message ... Dear Biff, thank you for your prompt response. I had tried your method but it won't show the second value of Bk-Alpha which reads at 99 and 95. Is there any way I can use to display all values of column 3? ie; show both 99 and 95 for bk-alpha when chosen. Thank you once again. B/Regards, Richmond "Biff" wrote: Hi! The ultra-easy way is to use a filter. Another way: Suppose your table is on sheet1 in the range A1:C4 In sheet2 A1 enter: Bk=Alpha In sheet2 B1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1))) Copy across to C1 then down until you get #NUM! errors meaning the data has been exhausted. Biff "Rich - SG" <Rich - wrote in message ... Dear Experts, I've a table of data and wonder whether or not it's possible to use Vlookup or Hlookup to find and list multiple rows of matching records. Please see example below: Name Supplier Cost Bk-Alpha ABC & Co 99.00 Bk-Beta 123 & Co 89.00 Bk-Apha XYZ & Co 95.00 From the above scenario, how do I go about to find "Bk-Alpha" and display all matching rows, ie: show ABC & Co, XYZ & Co and so on? I would really appreciate any kind help. Million thanks. Rich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to use the Prob function in a list of averages to find the le | Excel Worksheet Functions | |||
Extracting/look up data from a list and select multiple instances | Excel Worksheet Functions | |||
i need a function to find the highest value in a list | Excel Worksheet Functions | |||
how do i create a find function for an excel drop list? | Excel Discussion (Misc queries) | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) |