Lookup array inside the index range

ExcelNewbie2020

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
289
Office Version
  1. 365
Platform
  1. Windows
sirs,
i have an excel calendar format. i need to extract the notes based on names and dates. however, the names and dates will be within the index range if i use the index and match function. is there any other way to extract it? thanks


Name-List-for-Practice.xlsm
ABCDEFGHIJKLMN
11Monday2Tuesday3Wednesday4Thursday5Friday6Saturday7Sunday
2NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1OFFNAME1OFF
3NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2OFFNAME2OFF
4NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3OFFNAME3OFF
5
6
78Monday9Tuesday10Wednesday11Thursday12Friday13Saturday14Sunday
8NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1OFFNAME1OFF
9NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2OFFNAME2OFF
10NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3OFFNAME3OFF
11
12
13
14
15
16
17
18
19
20
21NAME1EXPECTED RESULT
2201-01-24MondayNOTE1
2302-01-24TuesdayNOTE1
2403-01-24WednesdayNOTE1
2504-01-24ThursdayNOTE1
2605-01-24FridayNOTE1
2706-01-24SaturdayOFF
2807-01-24SundayOFF
2908-01-24MondayNOTE1
3009-01-24TuesdayNOTE1
3110-01-24WednesdayNOTE1
3211-01-24ThursdayNOTE1
3312-01-24FridayNOTE1
3413-01-24SaturdayOFF
3514-01-24SundayOFF
Sheet4
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
You could try this, I repeat the user name on each row, however.

Book1
ABCDEFGHIJKLMN
11Monday2Tuesday3Wednesday4Thursday5Friday6Saturday7Sunday
2NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1OFFNAME1OFF
3NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2OFFNAME2OFF
4NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3OFFNAME3OFF
5
6
78Monday9Tuesday10Wednesday11Thursday12Friday13Saturday14Sunday
8NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1OFFNAME1OFF
9NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2OFFNAME2OFF
10NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3OFFNAME3OFF
20
21NAME1EXPECTED RESULTWeekdayEXPECTED RESULT
222024-01-01MondayNOTE1NAME12024-01-01MondayNOTE1
232024-01-02TuesdayNOTE1NAME12024-01-02TuesdayNOTE1
242024-01-03WednesdayNOTE1NAME12024-01-03WednesdayNOTE1
252024-01-04ThursdayNOTE1NAME12024-01-04ThursdayNOTE1
262024-01-05FridayNOTE1NAME12024-01-05FridayNOTE1
272024-01-06SaturdayOFFNAME12024-01-06SaturdayOFF
282024-01-07SundayOFFNAME12024-01-07SundayOFF
292024-01-08MondayNOTE1NAME12024-01-08MondayNOTE1
302024-01-09TuesdayNOTE1NAME12024-01-09TuesdayNOTE1
312024-01-10WednesdayNOTE1NAME12024-01-10WednesdayNOTE1
322024-01-11ThursdayNOTE1NAME12024-01-11ThursdayNOTE1
332024-01-12FridayNOTE1NAME12024-01-12FridayNOTE1
342024-01-13SaturdayOFFNAME12024-01-13SaturdayOFF
352024-01-14SundayOFFNAME12024-01-14SundayOFF
Sheet1
Cell Formulas
RangeFormula
H22:H35H22=XLOOKUP($E22,CHOOSECOLS(HSTACK($A$1:$N$4,$A$7:$N$10),MATCH($G22,HSTACK($A$1:$N$1,$A$7:$N$7),0)),CHOOSECOLS(HSTACK($A$1:$N$4,$A$7:$N$10),MATCH($G22,HSTACK($A$1:$N$1,$A$7:$N$7),0)+1))
 
Upvote 0
Solution
Another option
Fluff.xlsm
ABCDEFGHIJKLMN
101/01/202401/01/202402/01/202402/01/202403/01/202403/01/202404/01/202404/01/202405/01/202405/01/202406/01/202406/01/202407/01/202407/01/2024
2NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1OFFNAME1OFF
3NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2OFFNAME2OFF
4NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3OFFNAME3OFF
5
6
708/01/202408/01/202409/01/202409/01/202410/01/202410/01/202411/01/202411/01/202412/01/202412/01/202413/01/202413/01/202414/01/202414/01/2024
8NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1NOTE1NAME1OFFNAME1OFF
9NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2NOTE2NAME2OFFNAME2OFF
10NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3NOTE3NAME3OFFNAME3OFF
11
12
13
14
15
16
17
18
19
20
21NAME1EXPECTED RESULT
2201/01/2024MonNOTE1
2302/01/2024TueNOTE1
2403/01/2024WedNOTE1
2504/01/2024ThuNOTE1
2605/01/2024FriNOTE1
2706/01/2024SatOFF
2807/01/2024SunOFF
2908/01/2024MonNOTE1
3009/01/2024TueNOTE1
3110/01/2024WedNOTE1
3211/01/2024ThuNOTE1
3312/01/2024FriNOTE1
3413/01/2024SatOFF
3514/01/2024SunOFF
Sheet7
Cell Formulas
RangeFormula
C22:C35C22=LET(d,HSTACK($A$1:$N$4,$A$7:$N$10),f,FILTER(d,INDEX(d,1)=A22),TAKE(FILTER(f,TAKE(f,,1)=$A$21),,-1))
 
Upvote 1
You must log in or register to reply here.

Similar threads

T
  • Solved
Excel Time increment
Replies
9
Views
304
awoohaw
awoohaw
G
  • Question
Calendar view of production schedule
Replies
3
Views
344
felixstraube
F
V
  • Question
Looking to create VBA command to go through List of sheet names, look up sheet name in range, look up value in cell next to it, and run a macro
Replies
3
Views
108
Volrin
V
S
  • Solved
Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank
Replies
4
Views
93
staticfluids
S
S
  • Solved
Date time to Date conversion
Replies
4
Views
156
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,094
Messages
6,123,071
Members
449,092
Latest member
ipruravindra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top