Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help. Lookup or sum technic that returns a date. | Excel Discussion (Misc queries) | |||
Lookup which returns multiple values which are additive | Excel Worksheet Functions | |||
Lookup returns wrong value | Excel Worksheet Functions | |||
Lookup table returns #N/A | Excel Discussion (Misc queries) | |||
lookup returns row number-why? | Excel Worksheet Functions |