lookup intersection then move to the right

[Robert]

I would like to perform the following lookup using the index match formula:

Find the contents of the cell in column A of the current row on sheet1 in
row A on sheet2.
If not exist, leave blank.
Once I have identified the row on sheet2, then
Match the contents of the column header (the date) date in sheet2 that
corrosponds with the column header on sheet 1.
Give the contents of the cell that meets the 2 questions above.

However, the column header is a set of 5 merged cells. Formulas find the
number in only the far left cell of the merged set. Therefore, I think I need
a formula that not only finds the cell in question in the column at the left
most of the merged header
set but also be able to be used in other columns next to it.....perhaps
something like match the header in sheet1 to the header on sheet2 then move
right 1 cell, 2 cells, 3 cells, etc.

thanks in advance for any help,
Robert

 

[Frank Kabel]

Hi
best would be to get rid of merged cells. But for the
lookup try:
=INDEX(A1:X10,MATCH(lookup_row,A1:A10,0),MATCH
(lookup_column,A1:X1,0)+offset))

If you need to prevent errors use a formula such as:
=IF(ISNA(INDEX(...)),0,INDEX(...))