Lookup array inside the index range

ExcelNewbie2020

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
343
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

S
  • Solved
vlookup true range lookup
Replies
5
Views
213
etaf
etaf
R
  • Question Question
Leave Database Time Tracking to equal full day after 8 hours
Replies
6
Views
299
NoSparks
N
H
  • Question Question
Rolling 12 Months Array Sum
Replies
3
Views
500
Fluff
Fluff
A
  • Solved
Values Mismatch
Replies
3
Views
185
Alex Blakenburg
A
J
  • Question Question
Vlookup with multiple criteria + skip blank and find the next value
Replies
1
Views
426
StephenCrump
S

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

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