vlookup return multiple value

  • Thread starter Thread starter Jagaude
  • Start date Start date
J

Jagaude

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
 
Use AutoFilter. This allows you to present all the complete rows that match
a given criteria
 
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
 
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







- Show quoted text -
Click to expand...

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
 
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
 
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







- Show quoted text -
Click to expand...

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.
 
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
 
Back
Top