Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index, match in array setup question
good day,
i am working on trying to do an index/match within an array. the formula i have is: =INDEX(Sheet1!C:C,MATCH(1,(Sheet1!A:A=A5)*(Sheet1! B:B=Sheet2!B5),0)) with the { } around the formula. the problem that i want to over come is as follows: on the worksheet that i enter the formula, column A (cell A5 in the formula above) can have an item or critera that is anywhere from 1 to 13 characters long. On sheet 1, where i will be pulling the data from, it sources from Column A (A:A) but the report will only show the first 10 characters (from 1 up to 10, if longer than 10, the last three characters are dropped). The second column can be an exact match but this column can have duplicates. how can the formula be adjusted so that it matches the up to the first ten characters in column A, and an exact match in Column B? anything to get me going in the right direction would help. thank you, jat |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index, match in array setup question
I hope you have Excel 2007
=INDEX(Sheet1!C:C,MATCH(1,(Sheet1!A:A=MID(A5,1,MAX (10,A5))*(Sheet1!B:B=Sheet2!B5),0)) -- HTH Bob "jat" wrote in message ... good day, i am working on trying to do an index/match within an array. the formula i have is: =INDEX(Sheet1!C:C,MATCH(1,(Sheet1!A:A=A5)*(Sheet1! B:B=Sheet2!B5),0)) with the { } around the formula. the problem that i want to over come is as follows: on the worksheet that i enter the formula, column A (cell A5 in the formula above) can have an item or critera that is anywhere from 1 to 13 characters long. On sheet 1, where i will be pulling the data from, it sources from Column A (A:A) but the report will only show the first 10 characters (from 1 up to 10, if longer than 10, the last three characters are dropped). The second column can be an exact match but this column can have duplicates. how can the formula be adjusted so that it matches the up to the first ten characters in column A, and an exact match in Column B? anything to get me going in the right direction would help. thank you, jat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDEX and MATCH in an Array | Excel Discussion (Misc queries) | |||
Array formula with index + match | Excel Worksheet Functions | |||
Help with an Index Match Array | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Array index, match problem | Excel Worksheet Functions |