[spooky1]

I tried and I failed. see spreadsheet pls and if someone could point out the problem. Copied evrytink from MS website but it did not work.

[Keebellah]

What is the problem.
The formula is corect, it shows the smalles value for 'ash' that's exactly what the format does.

If you change SMALL to LARGE it will show the largest value
I re attached your file with both options
So there is nothing wrong with the formula but I think your interpretation of it.
What is it you're looking for?

[spooky1]

The title of the article on the MS website is:-
How to look up a value in a list and return multiple corresponding values

Okay, have mis understood, I assumed that multi value result meant showing more than one result like highlighted in bold at the bottom of this message .

What I want to do is show more than one value from col B, in example xls, in a cell.
Like ash is listed 2wice in Col A & has two differnt values for each in col B.

Is it possible to show lots of values in a cell if ash is in col A lots of times? If not lots then at least 3 values from col B?

Below copied from MS site
Return corresponding values from column B

Now, modify the preceding formula to return the corresponding value (and not the row number) and enter the formula as an array formula (CTRL+SHIFT+ENTER):
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2) The first corresponding value is 234, the value that corresponds to the first occurrence of the name "Ashish".
When you enter or fill this formula in subsequent cells, the formula returns the subsequent corresponding values of 534 and 834.


end



Just had something dropped on my head and saw a flash of light. Combine with & the lower value formula/code with the upper one listed on MS site? Would only give two values. Is there a mid function? If so I please provide code as I'm not smart enough to work it out. I'm very good at copy and paste though!


Perhaps there is another way to show many values if ash is entered in many times?

[Keebellah]

If it is just marking all the occurrences of ash you could do this using a conditional format based for example on the input value in a cell.

[Keebellah]

I have tried something using conditional format but do not know if it works with Excel 2003
You might get compatability issues

[Garf13LD]

if you copy your formula to the next cell below, it will show 3333.
If you want to show everything in one cell, you need a custom function.

[Keebellah]

Yeah, but the porster wants to see all the occurrences of the cell containing a name or part of a name. (see #3)

[spooky1]

In a list with many other names etc

col A col b
ash 6
ash 10
ash 11
result in one cell or msgbox `6 10 11' and more if there are any
is what I was looking for, clues anyone? Impossible?

That was interesting and I suppose I could use it by copying it one cell down in lieu of anything else
Thanks Garf and Keballah

[Keebellah]

Not with a formula but you can di this with a macro

[spooky1]

Hi Keballa,
doing it with a macro is fine, where do I find such a macro?

[Keebellah]

You write it!!!

[spooky1]

Thanks Keeballah, works like a charm.

[Keebellah]

you're welcome!

[spooky1]

Well, I managed, in your example.xls, to display more than one value using data in another sheet. But, when I tried to incorporate it into my program using right click `view code' I keep getting an ambiguos error message. I have lots of data in the change > Private Sub Worksheet_Change(ByVal Target As Range) of an existing sheet. I'm totally unfamiliar with the option explict commands.
I also want the code to work from data input via an inputbox or vba assigned value like x=104, lookup x rather than typing data in a cell on the sheet. I tried copying the option explicit code to a new module but nothing happens when I type something into a differently specified target cell. Am at a loss what to try next.

[Keebellah]

I'm away on holiday now, will be back in the course of the following week.
Option explicitr is nothing more than to force variables to be dimensioned.
Soma bacsic VBA knowledge is advisable if you want to use code such as macro's to make them do what you want them to do.
I suggest you take a look at some sites that will give you the basics and help you on the way.
I'll take a look when I'm back and see if I can help you along.
BTW ambiguous etc. etc means that you a duplication code.