Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Which Function to Use? Search an Array, Return a Row Value
I have an array of cells, B3:J34 on sheet2.
On sheet, 1, column, I have a range of values that can be found in the array on sheet2. I'd like to put a function on column b, sheet1 to return the value in row 2, above the searched value from sheet1 , column A. Does that make sense? Basically, I want to search for a value within an array, and return the value in row 2 when the searched value is located. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Which Function to Use? Search an Array, Return a Row Value
Assuming that Sheet1!A2 contains the lookup value, try the following
formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(Sheet2!$B$2:$J$2,MATCH(Sheet1!A2,INDEX(Shee t2!$B$3:$J$34,MIN(IF(Sh eet2!$B$3:$J$34=Sheet1!A2,ROW(Sheet2!$B$3:$J$34)-ROW(Sheet2!$B$3)+1)),0), 0)) Note that if the source data contains more than one occurrence of the lookup value, the value in Row 2 corresponding to the first occurrence will be returned. For example, if H5 and D5 contain the lookup value, the formula will return the value in H2. Hope this helps! In article .com, wrote: I have an array of cells, B3:J34 on sheet2. On sheet, 1, column, I have a range of values that can be found in the array on sheet2. I'd like to put a function on column b, sheet1 to return the value in row 2, above the searched value from sheet1 , column A. Does that make sense? Basically, I want to search for a value within an array, and return the value in row 2 when the searched value is located. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDEX function to return array row. | Excel Worksheet Functions | |||
Return array from worksheet function | Excel Worksheet Functions | |||
Search array and return element No | Excel Worksheet Functions | |||
Search a random array of cells and return a value of "X" | Excel Worksheet Functions | |||
Use array to return array of values | Excel Worksheet Functions |