Drop Down List Vlookup

[JDeMaro22]

Hi,

I'm looking for some type of formula to carry over a columns data when the date is selected from the drop down list. On the weekly analysis tab I would like the users to be able to select a week from the drop down list then the corresponding data is generated below it from the Dashboard worksheet.

Thank you again for your time,

Josh

QES Dashboard_MASTER.xlsm

[HansV]

Which version of Excel do you use / have to support?

[JDeMaro22]

365 Business, Version 2301

[HansV]

In B7:

=IF(INDEX(Dashboard!$G$7:$KU$86,SEQUENCE(80),MATCH(B6,Dashboard!$G$5:$KU$5,0))="","",INDEX(Dashboard!$G$7:$KU$86,SEQUENCE(80),MATCH(B6,Dashboard!$G$5:$KU$5,0)))

It will automatically spill to the rows below.

Fill to the right to B8.
I copied the formatting from the Dashboard sheet, then changed the background color.
See the attached version.

QES Dashboard_MASTER.xlsm

[JDeMaro22]

That is one complex formula, I didn't think it would be that difficult for you I apologize. Thank you very much it works perfectly.

[HansV]

It isn't that complicated. It looks complicated because the INDEX(...) part is repeated. The INDEX part is

INDEX(Dashboard!$G$7:$KU$86,SEQUENCE(80),MATCH(B6,Dashboard!$G$5:$KU$5,0))

This takes the entire range, returns all 80 rows by using SEQUENCE(80) = {1,2,3,..., 80} as row index, and only the desired column by using MATCH(B6,Dashboard!$G$5:$KU$5,0) as column index.
Unfortunately, this will return 0 if the source cell is empty. So we check if INDEX refers to an empty cell, and return "" if so, otherwise we return the result of INDEX.