Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with Hlookup - need to return values below the matched c
Not sure if this is even possible but here's the scenario.
Want to change date on worksheet 1, have the lookup function find the date match on worksheet 2 and then populate the various cells on worksheet 1 with the corresponding data on worksheet 2. Not sure how to return the location of the "matched" cell in the array for my lookup on 2nd worksheet. (i.e. date match is found on sheet 2 - cell E4 - that's what I want it to return). If I can get that I can use it in with an offset function to return the value of the cells I need. Maybe I'm not using the right functions at all - any help is appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with Hlookup - need to return values below the matched c
Dear
I understand that you want to get the row and column of the date Suppose in Sheet1 A1 you have the query date. Change the range in Sheet2 as your requirement. I have considered A1:J10 the below formula will retun the row =MIN(IF(Sheet2!A1:J10=A1,ROW(Sheet2!A1:J10))) the below formula will retun the column =MIN(IF(Sheet2!A1:J10=A1,ROW(Sheet2!A1:J10))) Now you should be able to get the relevant information from Sheet2 using OFFSET.. If you want to return the data itself..use the INDEX function =INDEX(Sheet2!A1:J10,MIN(IF(Sheet2!A1:J10=A1,ROW(S heet2!A1:J10))),MIN(IF(Sheet2!A1:J10=A1,COLUMN(She et2!A1:J10)))) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}". Well try and feedback... If this post helps click Yes --------------- Jacob Skaria "ACornell" wrote: Not sure if this is even possible but here's the scenario. Want to change date on worksheet 1, have the lookup function find the date match on worksheet 2 and then populate the various cells on worksheet 1 with the corresponding data on worksheet 2. Not sure how to return the location of the "matched" cell in the array for my lookup on 2nd worksheet. (i.e. date match is found on sheet 2 - cell E4 - that's what I want it to return). If I can get that I can use it in with an offset function to return the value of the cells I need. Maybe I'm not using the right functions at all - any help is appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with Hlookup - need to return values below the match
Perfect - managed to use this formula with both the indirect and offset
functions to get it to do exactly what I needed. Thank you!! "Jacob Skaria" wrote: Dear I understand that you want to get the row and column of the date Suppose in Sheet1 A1 you have the query date. Change the range in Sheet2 as your requirement. I have considered A1:J10 the below formula will retun the row =MIN(IF(Sheet2!A1:J10=A1,ROW(Sheet2!A1:J10))) the below formula will retun the column =MIN(IF(Sheet2!A1:J10=A1,ROW(Sheet2!A1:J10))) Now you should be able to get the relevant information from Sheet2 using OFFSET.. If you want to return the data itself..use the INDEX function =INDEX(Sheet2!A1:J10,MIN(IF(Sheet2!A1:J10=A1,ROW(S heet2!A1:J10))),MIN(IF(Sheet2!A1:J10=A1,COLUMN(She et2!A1:J10)))) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}". Well try and feedback... If this post helps click Yes --------------- Jacob Skaria "ACornell" wrote: Not sure if this is even possible but here's the scenario. Want to change date on worksheet 1, have the lookup function find the date match on worksheet 2 and then populate the various cells on worksheet 1 with the corresponding data on worksheet 2. Not sure how to return the location of the "matched" cell in the array for my lookup on 2nd worksheet. (i.e. date match is found on sheet 2 - cell E4 - that's what I want it to return). If I can get that I can use it in with an offset function to return the value of the cells I need. Maybe I'm not using the right functions at all - any help is appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return matched value 2 criteria | Excel Worksheet Functions | |||
Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
Return Title to matched column | New Users to Excel | |||
How to compare 2 lists and return un-matched? | Excel Worksheet Functions | |||
Hlookup to return a sum of values | Excel Worksheet Functions |