|
| 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. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 11th, 2009, 09:19 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index with mulitple value returns and muliple column returns
Using
=INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=Sheet1!$A$2,ROW(Sheet2!$A$1:$A$67)),ROW(1:1)),2) Is there a way to return multiple column values from the same row? 1 2 3 A w x y B q r s A m n o I want the return values to read across a row if looking for A: w x y m n o Thanks for any help. 
|
|
#2
June 11th, 2009, 11:07 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index with mulitple value returns and muliple column returns
Hi,
why not apply a AutoFilter on the column with A, B,... and filter for A. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "solar+CSE" wrote: Using =INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=Sheet1!$A$2,ROW(Sheet2!$A$1:$A$67)),ROW(1:1)),2) Is there a way to return multiple column values from the same row? 1 2 3 A w x y B q r s A m n o I want the return values to read across a row if looking for A: w x y m n o Thanks for any help.
|
|
#3
June 12th, 2009, 03:15 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index with mulitple value returns and muliple column returns
Try it like this...
Array entered =INDEX(Sheet2!B$1:B$67,SMALL(IF(Sheet2!$A$1:$A$67= Sheet1!$A$2,ROW(Sheet2!B$1:B$67)),ROWS(A$1:A1))) Copy across then down. That assumes your data really does start on row 1. If it doesn't then you'll need to tweak the formula. -- Biff Microsoft Excel MVP "solar+CSE" wrote in message ... Using =INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=Sheet1!$A$2,ROW(Sheet2!$A$1:$A$67)),ROW(1:1)),2) Is there a way to return multiple column values from the same row? 1 2 3 A w x y B q r s A m n o I want the return values to read across a row if looking for A: w x y m n o Thanks for any help.
|
|
#4
June 12th, 2009, 03:32 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index with mulitple value returns and muliple column returns
Thank you, that worked after some tweaking to my actual spreadsheet.
"T. Valko" wrote: Try it like this... Array entered =INDEX(Sheet2!B$1:B$67,SMALL(IF(Sheet2!$A$1:$A$67= Sheet1!$A$2,ROW(Sheet2!B$1:B$67)),ROWS(A$1:A1))) Copy across then down. That assumes your data really does start on row 1. If it doesn't then you'll need to tweak the formula. -- Biff Microsoft Excel MVP "solar+CSE" wrote in message ... Using =INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=Sheet1!$A$2,ROW(Sheet2!$A$1:$A$67)),ROW(1:1)),2) Is there a way to return multiple column values from the same row? 1 2 3 A w x y B q r s A m n o I want the return values to read across a row if looking for A: w x y m n o Thanks for any help.
|
|
#5
June 12th, 2009, 04:43 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index with mulitple value returns and muliple column returns
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "solar+CSE" wrote in message ... Thank you, that worked after some tweaking to my actual spreadsheet. "T. Valko" wrote: Try it like this... Array entered =INDEX(Sheet2!B$1:B$67,SMALL(IF(Sheet2!$A$1:$A$67= Sheet1!$A$2,ROW(Sheet2!B$1:B$67)),ROWS(A$1:A1))) Copy across then down. That assumes your data really does start on row 1. If it doesn't then you'll need to tweak the formula. -- Biff Microsoft Excel MVP "solar+CSE" wrote in message ... Using =INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=Sheet1!$A$2,ROW(Sheet2!$A$1:$A$67)),ROW(1:1)),2) Is there a way to return multiple column values from the same row? 1 2 3 A w x y B q r s A m n o I want the return values to read across a row if looking for A: w x y m n o Thanks for any help.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
