Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 671
Default lookup with multiple returns

hello and thanks to all. I have a question concerning a lookup with mult
returns.
I have a listing that keys on column A and I want to list the corresponding
values of columns B & C. Column A has multiples of the same "Key"

A B C
111 Contract 600
111 purchases 500
112 other costs 100
111 labor 200
112 Contract 500

any help will be greatly appreciated
--
Cheers!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default lookup with multiple returns

Hi,
If you sort by column A, will this will group all the data in the way you
want?
Dave.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default lookup with multiple returns

Can you clarify- do you want to have multiple returns when there is
more than one value of B and C for a given value of A, or just one?


I am assuming you have another sheet with a list of A values- and you
want to get the coresponding B and C value(s)


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 671
Default lookup with multiple returns

What i need to have as a product is a list on another worksheet of the the
data that are in Columns B & C. in other words, I want it to find all the
"112"s in Column A and then place the data that is in the columns B & C. If I
used the Vlookup, it will only produce the data that is associated with the
first "112". Using the formula i have now, i am getting a result from two
rows further down on my list.

=INDEX($B$3:$D$2135,SMALL(IF($B$3:$D$2135=$AW$63,R OW($B$3:$D$2135)),
ROW(1:1)),2)

Again, any assistance will be greatly appreciated, and sorry if I am a bit
hazy on presenting the problem. J
--
Cheers!


"James" wrote:

Can you clarify- do you want to have multiple returns when there is
more than one value of B and C for a given value of A, or just one?


I am assuming you have another sheet with a list of A values- and you
want to get the coresponding B and C value(s)



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default lookup with multiple returns

I want it to find all the "112"s in Column A
=INDEX($B$3:$D$2135,SMALL(IF($B$3:$D$2135=$AW$63, ROW($B$3:$D$2135)),ROW(1:1)),2)
Using the formula i have now, i am getting a result from
two rows further down on my list.


Try it like this:

Assume you enter the formula in cell F3:

=INDEX($C$3:$D$2135,SMALL(IF($B$3:$B$2135=$AW$63,R OW($B$3:$B$2135)),ROWS(F$3:F3))-MIN(ROW(B$3:B$2135))+1,COLUMNS($F3:F3))

Copy across to G3 then down until you get #NUM! errors.

Don't forget to array enter!

--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
hello and thanks to all. I have a question concerning a lookup with mult
returns.
I have a listing that keys on column A and I want to list the
corresponding
values of columns B & C. Column A has multiples of the same "Key"

A B C
111 Contract 600
111 purchases 500
112 other costs 100
111 labor 200
112 Contract 500

any help will be greatly appreciated
--
Cheers!





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default lookup with multiple returns

Hi,

I have posted a solution to a similar problem at the following link

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jay" wrote in message
...
hello and thanks to all. I have a question concerning a lookup with mult
returns.
I have a listing that keys on column A and I want to list the
corresponding
values of columns B & C. Column A has multiples of the same "Key"

A B C
111 Contract 600
111 purchases 500
112 other costs 100
111 labor 200
112 Contract 500

any help will be greatly appreciated
--
Cheers!


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default lookup with multiple returns

If the data doesn't start on row 1 then you have to calculate the offset in
order to match the correct *relative* row number of the INDEX function.

That's what the problem was with the OP's formula:

Using the formula i have now, i am getting a result from two rows further
down on my list.
=INDEX($B$3:$D$2135,SMALL(IF($B$3:$D$2135=$AW$63, ROW($B$3:$D$2135)),ROW(1:1)),2)


If the data doesn't start on row 1 then you have to make the adjustment to:

ROW($B$3:$D$2135)

So that the returned array corresponds to the "row positions" of the INDEX
function. The INDEX function starts at 1 while the ROW function starts at 3
so it'll be off by 2 as the OP noted.


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

I have posted a solution to a similar problem at the following link

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jay" wrote in message
...
hello and thanks to all. I have a question concerning a lookup with mult
returns.
I have a listing that keys on column A and I want to list the
corresponding
values of columns B & C. Column A has multiples of the same "Key"

A B C
111 Contract 600
111 purchases 500
112 other costs 100
111 labor 200
112 Contract 500

any help will be greatly appreciated
--
Cheers!




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default lookup with multiple returns

It's much more comprehensible, simpler, easier, and shorter, to just state
up front that the Row() function is simply creating buckets for the size of
the array.

Row 3 to row 2135 is 2,133 rows, so ...
Row(1:2133) is *all* that's needed ... short and sweet!

IMHO. that's much easier to explain to any OP!
--

Regards,

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

"T. Valko" wrote in message
...
If the data doesn't start on row 1 then you have to calculate the offset in
order to match the correct *relative* row number of the INDEX function.

That's what the problem was with the OP's formula:

Using the formula i have now, i am getting a result from two rows further
down on my list.
=INDEX($B$3:$D$2135,SMALL(IF($B$3:$D$2135=$AW$63, ROW($B$3:$D$2135)),ROW(1:1)),2)


If the data doesn't start on row 1 then you have to make the adjustment to:

ROW($B$3:$D$2135)

So that the returned array corresponds to the "row positions" of the INDEX
function. The INDEX function starts at 1 while the ROW function starts at 3
so it'll be off by 2 as the OP noted.


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

I have posted a solution to a similar problem at the following link

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jay" wrote in message
...
hello and thanks to all. I have a question concerning a lookup with mult
returns.
I have a listing that keys on column A and I want to list the
corresponding
values of columns B & C. Column A has multiples of the same "Key"

A B C
111 Contract 600
111 purchases 500
112 other costs 100
111 labor 200
112 Contract 500

any help will be greatly appreciated
--
Cheers!





  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default lookup with multiple returns

Row 3 to row 2135 is 2,133 rows, so ...
Row(1:2133) is *all* that's needed ... short and sweet!
IMHO. that's much easier to explain to any OP!


If you want to use that method then you should also include the caveat that
inserting new rows can break the formula.

IMHO, using ...ROW($B$3:$B$2135)),ROWS(F$3:F3))-MIN(ROW(B$3:B$2135))+1...
both accounts for row insertions and is the most "user-proof" method. And,
as an added bonus, you don't have to figure out that ROW(27:93) is 67 rows
or ROW(1:67).

--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
It's much more comprehensible, simpler, easier, and shorter, to just state
up front that the Row() function is simply creating buckets for the size
of
the array.

Row 3 to row 2135 is 2,133 rows, so ...
Row(1:2133) is *all* that's needed ... short and sweet!

IMHO. that's much easier to explain to any OP!
--

Regards,

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

"T. Valko" wrote in message
...
If the data doesn't start on row 1 then you have to calculate the offset
in
order to match the correct *relative* row number of the INDEX function.

That's what the problem was with the OP's formula:

Using the formula i have now, i am getting a result from two rows further
down on my list.
=INDEX($B$3:$D$2135,SMALL(IF($B$3:$D$2135=$AW$63 ,ROW($B$3:$D$2135)),ROW(1:1)),2)


If the data doesn't start on row 1 then you have to make the adjustment
to:

ROW($B$3:$D$2135)

So that the returned array corresponds to the "row positions" of the INDEX
function. The INDEX function starts at 1 while the ROW function starts at
3
so it'll be off by 2 as the OP noted.


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

I have posted a solution to a similar problem at the following link

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jay" wrote in message
...
hello and thanks to all. I have a question concerning a lookup with
mult
returns.
I have a listing that keys on column A and I want to list the
corresponding
values of columns B & C. Column A has multiples of the same "Key"

A B C
111 Contract 600
111 purchases 500
112 other costs 100
111 labor 200
112 Contract 500

any help will be greatly appreciated
--
Cheers!







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help. Lookup or sum technic that returns a date. sara Excel Discussion (Misc queries) 4 January 13th 07 02:40 PM
Lookup which returns multiple values which are additive ExcelMonkey Excel Worksheet Functions 1 January 3rd 07 09:52 PM
Lookup returns wrong value motorjobs Excel Worksheet Functions 5 June 21st 06 11:49 PM
Lookup table returns #N/A Kimberly Bassininsky Excel Discussion (Misc queries) 2 February 2nd 06 08:31 PM
lookup returns row number-why? Alan P Excel Worksheet Functions 4 September 22nd 05 11:23 AM


All times are GMT +1. The time now is 05:52 AM.

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

About Us

"It's about Microsoft Excel"