Prev: add two cells from seperate work sheets into a cell on seperat
Next: Significant digit causing wrong value?
From: Minnie on 27 Apr 2010 13:21

I had a question re duplicates as well:
I am trying to do a vlookup matching number figurers to their corresponding
year. However the years are duplicated:

Jan 1990 5555

feb 1990 4555

Mar 1990 4444

Jan 1991 54487

Feb 1991 2255

And so on...

i have a separate table with just the numbers. I am trying to match the
year to the numbers in the other table but am uable to use the vlookp due to
the duplicates.

Suggestions would be great!

Thanks!
From: The Rimalaya on 5 May 2010 04:50
"T. Valko" wrote:

> 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(a)discussions.microsoft.com> wrote in message
> news:07D5FF80-941A-42EE-A468-D497DA49E962(a)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
> >
> >
>
>

Cann't we do the same thing, if the data are not sorted... ??
From: Gemini...JV on 5 May 2010 12:50
I am having trouble with the following:

I have a sheet sorted in ID order and they have reported months next to them
(therefore there are duplicates ID).

Data source
2138 january 147
2138 february 161
2138 may 112
2138 june 191
2384 january 118
2384 february 119

New report
January february march april
2138
2384

I want to find a fuction where I need the 3rd column from source with ID and
Month matching.

Can you please help?


"T. Valko" wrote:

> Here's one way:
>
> Assume data in A2:B20. You want to extract data from column B that
> corresponds to a lookup_value.
>
> D2 = lookup_value
>
> Array entered** :
>
> =IF(ROWS($1:1)<=COUNTIF(A$2:A$20,D$2),INDEX(B$2:B$20,SMALL(IF(A$2:A$20=D$2,ROW(B$2:B$20)-MIN(ROW(B$2:B$20))+1),ROWS($1:1))),"")
>
> Copy down until you get blanks.
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER)
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "bonot1" <bonot1(a)discussions.microsoft.com> wrote in message
> news:8D2A39C6-255D-4110-95C6-44D3AAB4309D(a)microsoft.com...
> > Data is in random order, and the data to be returned is text.
> >
> > "T. Valko" wrote:
> >
> >> Is the data sorted so that the lookup_values are grouped together or is
> >> the
> >> data random? Is the data to be returned text or numeric?
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "bonot1" <bonot1(a)discussions.microsoft.com> wrote in message
> >> news:34F33288-D831-4FE6-89B6-657986F9255E(a)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, or at least a
> >> > function
> >> > that tells me there are duplicate matches?
> >>
> >>
> >>
>
>
>
From: G. on 19 May 2010 21:11
Hi I actually have another similar problem, can you help me?

Hi I actually have a similar problem. Could you help me: Basically. I want
to look up number 222222 in column A, and have it returns 3 values from
column B which are 666666, 777777, and 9999999 in 3 separate column. Please
help!


1....................A....................B
2..............123456...............1111111
3..............222222...............6666666
4..............222222...............7777777
5..............222222...............9999999
6..............444444...............8888888
From: ozgrid.com on 19 May 2010 21:18
Use a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Dave Hawley
www.ozgrid.com
"G." <G.(a)discussions.microsoft.com> wrote in message
news:CA004410-908E-40D4-B1AF-1D51CBA33A4E(a)microsoft.com...
> Hi I actually have another similar problem, can you help me?
>
> Hi I actually have a similar problem. Could you help me: Basically. I want
> to look up number 222222 in column A, and have it returns 3 values from
> column B which are 666666, 777777, and 9999999 in 3 separate column.
> Please
> help!
>
>
> 1....................A....................B
> 2..............123456...............1111111
> 3..............222222...............6666666
> 4..............222222...............7777777
> 5..............222222...............9999999
> 6..............444444...............8888888

 |  Next  |  Last
Pages: 1 2
Prev: add two cells from seperate work sheets into a cell on seperat
Next: Significant digit causing wrong value?