How can I lookup when match has more than one value?

farhad wrote:
> I am using LOOKUP functions to retrieve info from a list. Some of the lookup
> values have more than one match in the list. Is there a function that allows
> me to retrieve multiple elements for one lookup value? Offcourse the data is
> in random order, and the data to be returned is text. I need to have all the
> records that lookup retrieve it on the same rows.
>
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you might
want to consider the VLookups function. It is designed for this purpose.

Alan Beban

Look here:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

Unfortunately, the formulas are truncated. But the text explains very well what to do, so hopefully you can manage.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"farhad" <farhad DeleteThis @discussions.microsoft.com> wrote in message news:B4830F3C-F07B-49C2-85FD-A6414C9101D2@microsoft.com...
|I am using LOOKUP functions to retrieve info from a list. Some of the lookup
| values have more than one match in the list. Is there a function that allows
| me to retrieve multiple elements for one lookup value? Offcourse the data is
| in random order, and the data to be returned is text. I need to have all the
| records that lookup retrieve it on the same rows.
|

Hi I have a huge list of data that has items and each item has different
specifications. For example:


36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200

What I want to do is use a vlookup(34377007,$A$1:$B$8,4,FALSE)
The output that this vlookup will give me would be "Z28031-1" but in some
cases I want the information of the second row"5313312" or maybe the
third"4758766".
I have seen that there is explanations, and formulas that give you all the
info like this:
34377007 Z28031-1
5313312
4758766
But for the purpose of what i am doing I do not need all the values I just
need one of them.


for example:

Same vlookup, but I want the formula to give me the info from the second row
when it found the first value that match the vlookup

I do not know if exist a formula that makes this
vlookup(34377007,$A$1:$B$8,4(row2),FALSE)
=“5313312”

36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200






Same vlookup, but I want the formula to give me the info from the third row
when it found the first value that match the vlookup.
Vlookup (34377007, $A$1:$B$8, 4(row3),FALSE)
=“4758766”

36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200

If your data table is sorted or grouped together as is shown in your sample:

A2:D10 = data table

F2 = lookup value = 34377007
G2 = instance number = 2

=INDEX(D2:D10,MATCH(F2,A2:A10,0)+G2-1)

Result = 5313312

--
Biff
Microsoft Excel MVP


"Alfonso Valdes" <Alfonso Valdes.RemoveThis@discussions.microsoft.com> wrote in message
news:07D5FF80-941A-42EE-A468-D497DA49E962@microsoft.com...
> Hi I have a huge list of data that has items and each item has different
> specifications. For example:
>
>
> 36134008 R AA Z34116-1
> 36153004 R AA Z33021-1
> 34377007 P AA Z28031-1
> 34377007 P ZZ 5313312
> 34377007 P ZZ 4758766
> 36321027 P AA Z00000-1
> 36321027 P AA Z30918-1
> 36416003 P AA X32118-1
> 36421026 P ZZ 2394200
>
> What I want to do is use a vlookup(34377007,$A$1:$B$8,4,FALSE)
> The output that this vlookup will give me would be "Z28031-1" but in some
> cases I want the information of the second row"5313312" or maybe the
> third"4758766".
> I have seen that there is explanations, and formulas that give you all the
> info like this:
> 34377007 Z28031-1
> 5313312
> 4758766
> But for the purpose of what i am doing I do not need all the values I
> just
> need one of them.
>
>
> for example:
>
> Same vlookup, but I want the formula to give me the info from the second
> row
> when it found the first value that match the vlookup
>
> I do not know if exist a formula that makes this
> vlookup(34377007,$A$1:$B$8,4(row2),FALSE)
> ="5313312"
>
> 36134008 R AA Z34116-1
> 36153004 R AA Z33021-1
> 34377007 P AA Z28031-1
> 34377007 P ZZ 5313312
> 34377007 P ZZ 4758766
> 36321027 P AA Z00000-1
> 36321027 P AA Z30918-1
> 36416003 P AA X32118-1
> 36421026 P ZZ 2394200
>
>
>
>
>
>
> Same vlookup, but I want the formula to give me the info from the third
> row
> when it found the first value that match the vlookup.
> Vlookup (34377007, $A$1:$B$8, 4(row3),FALSE)
> ="4758766"
>
> 36134008 R AA Z34116-1
> 36153004 R AA Z33021-1
> 34377007 P AA Z28031-1
> 34377007 P ZZ 5313312
> 34377007 P ZZ 4758766
> 36321027 P AA Z00000-1
> 36321027 P AA Z30918-1
> 36416003 P AA X32118-1
> 36421026 P ZZ 2394200
>
>

I have also tried to use this article, but I am getting "#NUM" error values
using the "SMALL" mixed with the "ROW" functions. I am following the example
verbatim, and I do not think this should be that difficult. This article
attempts to do EXACTLY what I am looking for.

Does anyone know of where the actual example can be downloaded as some of
the formulas are truncated?

Regards,

Mike

"Niek Otten" wrote:

> Look here:
>
> http://office.microsoft.com/en-us/excel/HA012260381033.aspx
>
> Unfortunately, the formulas are truncated. But the text explains very well what to do, so hopefully you can manage.
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "farhad" <farhad RemoveThis @discussions.microsoft.com> wrote in message news:B4830F3C-F07B-49C2-85FD-A6414C9101D2@microsoft.com...
> |I am using LOOKUP functions to retrieve info from a list. Some of the lookup
> | values have more than one match in the list. Is there a function that allows
> | me to retrieve multiple elements for one lookup value? Offcourse the data is
> | in random order, and the data to be returned is text. I need to have all the
> | records that lookup retrieve it on the same rows.
> |
>
>
>

I don't know what article you are talking about? I have an example that can
be downloaded
from here


http://www.nwexcelsolutions.com/advanced_function_page.htm#6._You_can_..._a_comb


here's the direct link below




http://nwexcelsolutions.com/Download/Return%20more%20than%20one%20valu...0from%2

--


Regards,


Peo Sjoblom

"MRLYONS68" <MRLYONS68.TakeThisOut@discussions.microsoft.com> wrote in message
news:F0E5F399-E84D-4CE5-A567-6CB78DF73FFF@microsoft.com...
>I have also tried to use this article, but I am getting "#NUM" error values
> using the "SMALL" mixed with the "ROW" functions. I am following the
> example
> verbatim, and I do not think this should be that difficult. This article
> attempts to do EXACTLY what I am looking for.
>
> Does anyone know of where the actual example can be downloaded as some of
> the formulas are truncated?
>
> Regards,
>
> Mike
>
> "Niek Otten" wrote:
>
>> Look here:
>>
>> http://office.microsoft.com/en-us/excel/HA012260381033.aspx
>>
>> Unfortunately, the formulas are truncated. But the text explains very
>> well what to do, so hopefully you can manage.
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>> Microsoft MVP - Excel
>>
>> "farhad" <farhad.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:B4830F3C-F07B-49C2-85FD-A6414C9101D2@microsoft.com...
>> |I am using LOOKUP functions to retrieve info from a list. Some of the
>> lookup
>> | values have more than one match in the list. Is there a function that
>> allows
>> | me to retrieve multiple elements for one lookup value? Offcourse the
>> data is
>> | in random order, and the data to be returned is text. I need to have
>> all the
>> | records that lookup retrieve it on the same rows.
>> |
>>
>>
>>