|
Index Match Match?
This one just seems to stump me!
What I have in the cell is: =INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet1!B2:B1000,0)) and it gives me the eternal #REF! What am I JUST NOT GETTING? Thanks Hans |
Index Match Match?
Shouldn't the range
Sheet1!C2:C1000 span mutiple columns as well? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... This one just seems to stump me! What I have in the cell is: =INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet 1!B2:B1000,0)) and it gives me the eternal #REF! What am I JUST NOT GETTING? Thanks Hans |
Index Match Match?
Try this *array* formula:
=INDEX(Sheet1!C2:C1000,MATCH(1,(D104=Sheet1!A2:A10 00)*(Sheet3!E104=Sheet1!B2:B1000),0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message oups.com... This one just seems to stump me! What I have in the cell is: =INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet1!B2:B1000,0)) and it gives me the eternal #REF! What am I JUST NOT GETTING? Thanks Hans |
Index Match Match?
Thanks Guys....Got it working!!
Hans RagDyer wrote: Try this *array* formula: =INDEX(Sheet1!C2:C1000,MATCH(1,(D104=Sheet1!A2:A10 00)*(Sheet3!E104=Sheet1!B2:B1000),0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message oups.com... This one just seems to stump me! What I have in the cell is: =INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet1!B2:B1000,0)) and it gives me the eternal #REF! What am I JUST NOT GETTING? Thanks Hans |
Index Match Match?
Thanks for the feed-back.
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- wrote in message ps.com... Thanks Guys....Got it working!! Hans RagDyer wrote: Try this *array* formula: =INDEX(Sheet1!C2:C1000,MATCH(1,(D104=Sheet1!A2:A10 00)*(Sheet3!E104=Sheet1!B2 :B1000),0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - wrote in message oups.com... This one just seems to stump me! What I have in the cell is: =INDEX(Sheet1!C2:C1000,MATCH(D104,Sheet1!A2:A1000, 0),MATCH(Sheet3!E104,Sheet 1!B2:B1000,0)) and it gives me the eternal #REF! What am I JUST NOT GETTING? Thanks Hans |
| All times are GMT +1. The time now is 01:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com