multiple lookups using index small

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
453
I need a formula for going across the columns

using this array formula {=INDEX($I$2:$I$21,SMALL(IF($H$2:$H$21=$A$2,ROW($H$2:$H$21)-1),ROW()-1))}

this works when copying the formula down a column
Vliookup differant rows New.xls
ABCDEFGHI
1
2Monday1Monday1
33Tuesday2
44Monday3
511Monday4
618Tuesday5
7#NUM!Wednesday6
8#NUM!Thursday7
9#NUM!Friday8
10#NUM!Saturday9
11#NUM!Sunday10
12Monday11
13Tuesday12
14Wednesday13
15Thursday14
16Friday15
17Saturday16
18Sunday17
19Monday18
20Tuesday19
21Wednesday20
Sheet3




What I want is to amend the formula so I can copy it from B2 To G2
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Try:
=INDEX($I$2:$I$21,SMALL(IF($H$2:$H$21=$A$2,ROW($H$2:$H$21)-1),COLUMNS($B2:B2)))
Still confirmed with Ctrl+Shift+Enter
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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