Search:

Help required in Vlookup

Asked By: Robert    Date: Nov 19    Category: MS Office    Views: 2009

I need a help in vlookup. Vlookup gives only the first occurrence of a value.
I need all the occurrences in a particular cell. I have attached an excel sheet
with details. Column A and B is the table array and the lookup value is in
column C. Column D has the normal vlookup, however the data i need should be
like how it looks in column E.

Share:

I have had NONVBA success with the instructions under "Arbitray Lookup"
in the following:

http://www.cpearson.com/excel/lookups.htm

I tried some things (functions: MATCH & COUNTIF) and have the

First, VLOOKUP finds the first occurrance, butalso requires that
the table  be sorted so column  1 is in ascending order. This will also
put all same names together which helps obtain your desired result.
Then, the Function COUNTIF can find how many times a Name is in
your table -- and you can get their numbered locatons.

You could do the following.
0- Add another column which is numberd sequentially (just as your
column B happens to be) I'll call it Column "C"

1- Sort the *WHOLE* table (all three columns) by Name, ascending. If
you need it in the original order you can simply "un-sort" by the
column "C" (added in the above step). Or copy the table to another
location or sheet and sort only one of the copies.

2- Use VLOOKUP to locate the first occurrance of a name, in the
*SORTED* list. You'll have to decide just how you want to note this
location. You can add yet another column which is numbered
sequentially AFTER the sort. OR you can use the ROW number of the
first occurrance.

3- Use the function COUNTIF to determine how many of a given Name is
in the Name column of the table.
For example, paste this into your cell  "G3":
=COUNTIF(A2:A14,C3)
and copy it down for 4 more cells to obtain a count of each of your
names. It will show values of 1, 2, 3, 3, 2 in your sheet.

4- Use a loop to read the location of the occurrances by looking in
the addeed column "C". Do this for the next "COUNTIF" occurrances;
that is, the number of them given by the COUNTIF.

It will look *Something* like this Pseudo Code:

= = = = = = = = Find items = = = = = = = = = =
Dim Location As Integer
Dim X As Integer

Sort *complete* Table By Col "C"

Location = First location (Row) of "Name" found with VLOOKUP

For X = 0 to [THE COUNTIF value]
Select cell offset from "Location" by X rows. (Offset Method)
Data_Required = Data_Required & ", " & Column "C".Value
' The above builds your numbered list W/ commas.
Next

Save Data_Required String in your column "E" (labeld data  Required).

Note:
Although the variable "Location" is a number (Integer) and the
Data_Required" is a String, VBA will do the conversion from number to
String for you in the last line. This allows using the String
Concatination operator "&". If you want to do it explicitly, use the
Convert to String function "Cstr()". The last line then looks like this.
Data_Required = Data_Required & ", " & Cstr(Column "C".Value)

Didn't find what you were looking for? Find more on Help required in Vlookup Or get search suggestion and latest updates.