Need help with Hlookup - need to return values below the matched c

[ACornell]

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!

 

[Jacob Skaria]

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(Sheet2!A1:J10))),MIN(IF(Sheet2!A1:J10=A1,COLUMN(Sheet2!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

 

[ACornell]

Perfect - managed to use this formula with both the indirect and offset
functions to get it to do exactly what I needed.

Thank you!!