value of merged cell next to a non-merged one [closed]
I have a table where column B contains individual data and column A has several merged cells that informally group the B cells. Let's say B contains transaction IDs and A contains merged cells where each merged cell contains a date string for the time ordered transactions.
In another sheet in whatever way I get a reference to a B cell (for example the output of a function is sheet1.B42). I need to get the A value for that B value. For example B42 happens to be next to the A38:A52 merged cell. That is I need the value of A38.
To sum up: sheet2.A1 -> function with a value of "sheet1.B42" (result of a lookup) sheet1.B42 is next to the merged cell A38:A52 sheet2.B1 should get the value of sheet1.A38
How do I make that sheet2.A1 -> sheet1.A38 connection?