vlookup return multiple value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jagaude - 27 Feb 2008 18:44 GMT

Hi, this is my first post! Need some help with the vlookup formula.

My range is between A1:B8. Colon A represent numbers and colon B
represent names. How can I vlookup colon A for a specific number and
return all names in colon B with that number?

Thanks,

Jagaude

Reply to this Message

Gary''s Student - 27 Feb 2008 19:31 GMT

Use AutoFilter. This allows you to present all the complete rows that match
a given criteria

Signature

Gary''s Student - gsnu2007d

> Hi, this is my first post! Need some help with the vlookup formula.
>
[quoted text clipped - 5 lines]
>
> Jagaude

Reply to this Message

Pete_UK - 27 Feb 2008 22:26 GMT

Suppose you have something like this in A1:B10:

7 Alan
5 Brian
5 Colin
7 David
7 Eddie
1 Frank
3 George
5 Harry
7 Ian
3 John

Put this formula in C1 and copy it down to C10:

=A1&"_"&COUNTIF(A$1:A1,A1)

This will give you a sequence like this:

7_1
5_1
5_2
7_2
7_3
1_1
3_1
5_3
7_4
3_2

Then suppose you want to enter a number in E1 and have all the names
matching that number appear in column F. Put this formula in F1 and copy it
down for as many rows as you think you might need:

=IF(ISNA(MATCH(E$1&"_"&ROW(A1),C$1:C$10,0)),"",INDEX(B$1:B$10,MATCH(E$1&"_"&ROW(A1),C$1:C$10,0)))

Then if you put 7 in E1 you will get this in column F:

Alan
David
Eddie
Ian

Change E1 to 3, and you will get:

George
John

Is that what you wanted?

Hope this helps.

Pete

> Hi, this is my first post! Need some help with the vlookup formula.
>
[quoted text clipped - 5 lines]
>
> Jagaude

Reply to this Message

Jagaude - 28 Feb 2008 14:58 GMT

> Suppose you have something like this in A1:B10:
>
[quoted text clipped - 61 lines]
>
> - Show quoted text -

Hi Pete,

That's great. I wish I could understand the formula better but I'll
take it and run. I just have one more question : This formula works
only if the cells are on top of the page, if I move those cell to
another location like starting on A10 and so on, the formula returns
blank. Can you help.

Thank you for the quick responce and great work. Keep it up.

Cheers,

Jagaude

Reply to this Message

Pete_UK - 28 Feb 2008 15:51 GMT

Okay, assume your data now starts in A10 and goes down to B19 - the
first formula I gave you should be this in C10:

=A10&"_"&COUNTIF(A$10:A10,A10)

and copy this down to C19. Notice that all the row references are the
same as the starting row.

Suppose now that the number you input is in E5, then the formula in F5
would become:

=IF(ISNA(MATCH(E$5&"_"&ROW(A1),C$10:C$19,0)),"",INDEX(B$10:B$19,MATCH(E
$5&"_"&ROW(A1),C$10:C$19,0)))

and again this is copied down as many rows as you think you might
need.

In a real case your range is likely to be much larger, so assuming you
still have the same columns as I've assumed, then any reference to C
$10:C$19 or B$10:B$19 should be changed to suit your data. If you have
2000 rows, for example, this would become C$10:C$2009. Note that the E
$5 relates to the cell where you want to enter your search number, and
A1 is used for the first row the formula is put in - it will change as
you copy the formula down.

Hope this helps.

Pete

> > Suppose you have something like this in A1:B10:
>
[quoted text clipped - 81 lines]
>
> - Show quoted text -

Reply to this Message

Jagaude - 28 Feb 2008 20:48 GMT

> Okay, assume your data now starts in A10 and goes down to B19 - the
> first formula I gave you should be this in C10:
[quoted text clipped - 112 lines]
>
> - Show quoted text -

Hi Pete,

Thank you, I'll play with it. Now here's another good challenge for
you. If I would like the formula to return the names associated with a
value greater than 3. So basically, return the names that have the
value 3, 5 and 7 from the example.

Thanks,

Jagaude.

Reply to this Message

Pete_UK - 29 Feb 2008 01:32 GMT

You just need to make a change to the first formula I gave you.
Assuming the set up as in the latest scenario, i.e. with the data
starting in A10 and the number of interest in E5, then put this
amended formula in C10:

=IF(A10>=E$5,E$5&"_"&COUNTIF(A$10:A10,">="&E$5),A10&"_"&COUNTIF(A
$10:A10,A10))

and copy down. The sequence in column C will change, depending on the
value in E5, but the names appearing in column F will reflect your
latest requirements, i.e. all those with numbers in column A which are
greater than or equal to the number in E5. You may need to copy the
formula in column F down more rows to accomodate the increased number
of names.

Hope this helps.

Pete

> > Okay, assume your data now starts in A10 and goes down to B19 - the
> > first formula I gave you should be this in C10:
[quoted text clipped - 125 lines]
>
> - Show quoted text -

Reply to this Message

Rate this thread: