Fetching Multiple values using VLOOKUP

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

Hi,
I know that VLOOKUP can return only the first value it
finds.But i have to generate a report where i search using
a number and that number repeats again and again and i
have to get the value each time the number occurs.
For Eg.
I have a raw data which has the following headings
Badge No,Name,work done.
And i am searching using the badge number.
The work done column will have many values for the same
badge number.
So i need to get each and every value under the work done
to be captured and reflecting in the output with the name.
Is it possible..Thanks in advance for any of your help
 
Hi Patrick

Could you not just use Autofilter
Tools=>Data=>Autofilter
Select Badge Number from the drop down on that column and all rows containg
that number will show.
 
Patrick said:
Hi,
I know that VLOOKUP can return only the first value it
finds.But i have to generate a report where i search using
a number and that number repeats again and again and i
have to get the value each time the number occurs.
For Eg.
I have a raw data which has the following headings
Badge No,Name,work done.
And i am searching using the badge number.
The work done column will have many values for the same
badge number.
So i need to get each and every value under the work done
to be captured and reflecting in the output with the name.
Is it possible..Thanks in advance for any of your help
Click to expand...

Autofilter is one possibility that has already been suggested.
If you need a solution using worksheet formulas, you will need one formula
for each possible result. Have a look here in the paragraph "Arbitrary
Lookups" for how this is done:
http://www.cpearson.com/excel/lookups.htm
 
Badge number in Column A
Name in Column B
Work in Column C
Labels in Row 1
Badge number to look up in D1
Data A2:C100

Enter this formula in D2:

=INDEX(C2:C100,SMALL(IF(A2:A100=D1,ROW(A2:A100)-1,""),ROW(A2:A100)-1))

Now, if you anticipate that a badge number may repeat 10 times, select D2
and drag down to D15 (13 rows), just to make sure no badge number will be
omitted, and then hit F2, and do CSE (<Ctrl> <Shift> <Enter>) in order to
make this an array formula.
If it's done correctly, the formulas will automatically be enclosed in curly
brackets.

If there are only 10 badge numbers to match D1, the additional rows
containing your formulas will return a #NUM! error.
If there are no errors returned, you should extend your formula rows in
order to insure that you returned all the numbers that are present.
For this reason, you will always want to see at least one error.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




Hi,
I know that VLOOKUP can return only the first value it
finds.But i have to generate a report where i search using
a number and that number repeats again and again and i
have to get the value each time the number occurs.
For Eg.
I have a raw data which has the following headings
Badge No,Name,work done.
And i am searching using the badge number.
The work done column will have many values for the same
badge number.
So i need to get each and every value under the work done
to be captured and reflecting in the output with the name.
Is it possible..Thanks in advance for any of your help
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, with

Badge number in Column A
Name in Column B
Work in Column C
Labels in Row 1
Badge number to look up in D1
Data A2:C100

=VLookups(D1,A2:C100,3) array entered into a column of enough rows to
accommodate the output.

Alan Beban
 
Back
Top