A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #51  
Old May 5th, 2010, 09:50 AM posted to microsoft.public.excel.worksheet.functions
The Rimalaya
external usenet poster
 
Posts: 1
Default How can I lookup when match has more than one value?

"T. Valko" wrote:

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

A210 = data table

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

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

Result = 5313312

--
Biff
Microsoft Excel MVP


"Alfonso Valdes" Alfonso wrote in message
...
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... ??
  #52  
Old May 5th, 2010, 05:50 PM posted to microsoft.public.excel.worksheet.functions
Gemini...JV
external usenet poster
 
Posts: 1
Default How can I lookup when match has more than one value?

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" wrote in message
...
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" wrote in message
...
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?






  #53  
Old May 20th, 2010, 02:11 AM posted to microsoft.public.excel.worksheet.functions
G.[_2_]
external usenet poster
 
Posts: 1
Default How can I lookup when match has more than one value?

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
  #54  
Old May 20th, 2010, 02:18 AM posted to microsoft.public.excel.worksheet.functions
ozgrid.com
external usenet poster
 
Posts: 328
Default How can I lookup when match has more than one value?

Use a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Dave Hawley
www.ozgrid.com
"G." wrote in message
...
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


  #55  
Old May 20th, 2010, 05:12 PM posted to microsoft.public.excel.worksheet.functions
G.
external usenet poster
 
Posts: 39
Default How can I lookup when match has more than one value?


Hi,
I have used Pivot table and it only shows those 3 values in vertical way (1
column)..but i want the values to be shown horizontally (3 columns). Do you
know if Pivot table can do that?

Thanks,
G.



"ozgrid.com" wrote:

Use a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Dave Hawley
www.ozgrid.com
"G." wrote in message
...
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


.

  #56  
Old May 24th, 2010, 10:00 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default How can I lookup when match has more than one value?

Hi

Drag B to the Column area
--
Regards
Roger Govier

G. wrote:
Hi,
I have used Pivot table and it only shows those 3 values in vertical way (1
column)..but i want the values to be shown horizontally (3 columns). Do you
know if Pivot table can do that?

Thanks,
G.



"ozgrid.com" wrote:

Use a PivotTable
http://www.ozgrid.com/Excel/excel-pivot-tables.htm



--
Regards
Dave Hawley
www.ozgrid.com
"G." wrote in message
...
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

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.