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