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

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

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

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

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

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