Lookup data across multiple tables

[buzzbee]

I'm stuck! What I want to do seems like it would be simple, but I am struggling!

I want to look up values and return values 2 cells below, much like using hlookup. However, the lookup values are not all in the same row. I have attached an example. As you can see, there is basically a table for each month. What I want to do is look up each date across all months and return a value 2 cells below.

It's probably something simple, but I just can't get it

Thanks

[QuieT]

Hiya! This worked for me: =XLOOKUP(B26,$B$2:$AF$2,$B$4:$AF$4,0,0,1). If you were to put your data next to each other, instead of underneath, you would be able to drag the formula to cover wherever you want it to go. Otherwise, you need to change the range once you've finished up to and including AF40.

[buzzbee]

Thank you QuieT
Unfortunately, neither moving the data to all be alongside or changing the formula after AF40 are an option for me. The example I attached is just a very scaled down example. The worksheet on which I will eventually use it contains hundreds of columns and thousands of rows

[ChemistB]

Okay, the hundreds of columns throws me because my solution resolves around having 1 month a row. Maybe describe how your data will be set up a little more.

XLOOKUP will only work online (for now) and if you have Office 365. This works for your example

Please Login or Register  to view this content.

[Limor_OP]

Hi
Use this in D26 and down:

=INDEX($B$1:$AF$20,SMALL(IF($B$2:$AF$20=B26,ROW($B$2:$AF$20)+2),1),SMALL(IF($B$2:$AF$20=B26,COLUMN($B$2:$AF$20)),1)-1)

[Limor_OP]

The former is an array formula. You can use this instead In D26 and down:

=INDEX($B$1:$AF$20,AGGREGATE(14,6,(($B$2:$AF$20=B26)*ROW($B$2:$AF$20)),1)+2,AGGREGATE(14,6,(($B$2:$AF$20=B26)*COLUMN($B$2:$AF$20)),1)-1)

[buzzbee]

Okay, the hundreds of columns throws me because my solution resolves around having 1 month a row. Maybe describe how your data will be set up a little more.

XLOOKUP will only work online (for now) and if you have Office 365. This works for your example

Please Login or Register  to view this content.

Thank you ChemistB. Looking at what you have done makes me realise that using an example of consecutive days, separated by month complicated the issue, rather than simplifying it as I had expected. In the actual file I was using, as days went across, they were 7 days across, not just 1, starting in June. Each section had 50 days across (hundreds of columns, because they were merged cells, due to the data in the rows beneath), so your formula, which would be good in what I now see as a flawed example, wouldn't work in the main file. However, it has given me a better understanding of using the date function, with the year and month functions within, so has still been a help to me. thank you for your time

[buzzbee]

Hi
Use this in D26 and down:

=INDEX($B$1:$AF$20,SMALL(IF($B$2:$AF$20=B26,ROW($B$2:$AF$20)+2),1),SMALL(IF($B$2:$AF$20=B26,COLUMN($B$2:$AF$20)),1)-1)

The former is an array formula. You can use this instead In D26 and down:

=INDEX($B$1:$AF$20,AGGREGATE(14,6,(($B$2:$AF$20=B26)*ROW($B$2:$AF$20)),1)+2,AGGREGATE(14,6,(($B$2:$AF$20=B26)*COLUMN($B$2:$AF$20)),1)-1)

Belinda, you've come up trumps again. Both of your suggestions have worked a treat! Thank you