VLOOKUP Repeating Numbers

P

Pippy79

Board Regular
Joined
Nov 18, 2021
Messages
90
Office Version
  1. 365
Platform
  1. Windows
Hi,

i have a long list of data that is split out over months. Each month has 20 slots numbered 1- 20, the numbers then start again when we start a new number. each slot has a batch number that changes for each batch and is never repeated.

below shows the list of slots and changes in dates, i would like to know if there is a way to lookup slot 1 in feb and display a batch number, then also when march comes around display the different batch number for slot 1 in march.

ABCD
1​
SlotModuleStart DateBatch Number
2​
14Thu, 03-Feb-2022
3​
4​
24Thu, 03-Feb-2022
5​
6​
37Fri, 04-Feb-2022
7​
8​
47Sun, 06-Feb-2022
9​
10​
57Thu, 10-Feb-2022
11​
12​
14Thu, 03-Mar-2022
13​
14​
27Fri, 04-Mar-2022
15​
16​
34Fri, 04-Mar-2022
17​
18​
44Sun, 06-Mar-2022
19​
20​
54Thu, 10-Mar-2022

also below is the table where the data is stored, this is a separate sheet;
ABC
1​
SLOTMFT STARTBatch Number
2​
1Thu, 03-Feb-2022
100​
3​
2Thu, 03-Feb-2022
101​
4​
3Fri, 04-Feb-2022
102​
5​
4Sun, 06-Feb-2022
103​
6​
5Thu, 10-Feb-2022
104​
7​
1Thu, 03-Mar-2022
105​
8​
2Fri, 04-Mar-2022
106​
9​
3Fri, 04-Mar-2022
107​
10​
4Sun, 06-Mar-2022
108​
11​
5Thu, 10-Mar-2022
109​

for example in the first table in cell D2 i want it to look up from the other table and display "100", then when i get to slot 1 in D12 it should display "105"

Thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
will this works for you?

Book1
ABCDEFGH
1SlotModuleStart DateBatch NumberSLOTMFT STARTBatch Number
214Thu, 03-Feb-20221001Thu, 03-Feb-2022100
3 2Thu, 03-Feb-2022101
424Thu, 03-Feb-2022 3Fri, 04-Feb-2022102
5 4Sun, 06-Feb-2022103
637Fri, 04-Feb-2022 5Thu, 10-Feb-2022104
7 1Thu, 03-Mar-2022105
847Sun, 06-Feb-2022 2Fri, 04-Mar-2022106
9 3Fri, 04-Mar-2022107
1057Thu, 10-Feb-2022 4Sun, 06-Mar-2022108
11 5Thu, 10-Mar-2022109
1214Thu, 03-Mar-2022105
13 
1427Fri, 04-Mar-2022 
15 
1634Fri, 04-Mar-2022 
17 
1844Sun, 06-Mar-2022 
19 
2054Thu, 10-Mar-2022 
Sheet1
Cell Formulas
RangeFormula
D2:D20D2=IF(A2=1,INDEX($H$2:$H$11,MATCH(C2,$G$2:G11,0)),"")
 
Upvote 0
Try

Sheet1

Book1
ABCDE
1ABCD
21SlotModuleStart DateBatch Number
3214Thu, 03-Feb-2022100
43 
5424Thu, 03-Feb-2022101
65 
7637Fri, 04-Feb-2022102
87 
9847Sun, 06-Feb-2022103
109 
111057Thu, 10-Feb-2022104
1211 
131214Thu, 03-Mar-2022105
1413 
151427Fri, 04-Mar-2022106
1615 
171634Fri, 04-Mar-2022107
1817 
191844Sun, 06-Mar-2022108
2019 
212054Thu, 10-Mar-2022109
Sheet1
Cell Formulas
RangeFormula
E3:E21E3=IFERROR(INDEX(Sheet2!$D$3:$D$12,AGGREGATE(15,6,(ROW(Sheet2!$D$3:$D$12)-ROW(Sheet2!$D$3)+1)/(Sheet2!$B$3:$B$12=Sheet1!B3),COUNTIF(Sheet1!$B$3:B3,Sheet1!B3))),"")


Sheet2

Book1
ABCDE
1ABC
21SLOTMFT STARTBatch Number
321Thu, 03-Feb-2022100
432Thu, 03-Feb-2022101
543Fri, 04-Feb-2022102
654Sun, 06-Feb-2022103
765Thu, 10-Feb-2022104
871Thu, 03-Mar-2022105
982Fri, 04-Mar-2022106
1093Fri, 04-Mar-2022107
11104Sun, 06-Mar-2022108
12115Thu, 10-Mar-2022109
13
14
15
16
17
18
19
20
21
Sheet2
 
Upvote 0
Solution
Sufiyan97 said:
Try

Sheet1

Book1
ABCDE
1ABCD
21SlotModuleStart DateBatch Number
3214Thu, 03-Feb-2022100
43 
5424Thu, 03-Feb-2022101
65 
7637Fri, 04-Feb-2022102
87 
9847Sun, 06-Feb-2022103
109 
111057Thu, 10-Feb-2022104
1211 
131214Thu, 03-Mar-2022105
1413 
151427Fri, 04-Mar-2022106
1615 
171634Fri, 04-Mar-2022107
1817 
191844Sun, 06-Mar-2022108
2019 
212054Thu, 10-Mar-2022109
Sheet1
Cell Formulas
RangeFormula
E3:E21E3=IFERROR(INDEX(Sheet2!$D$3:$D$12,AGGREGATE(15,6,(ROW(Sheet2!$D$3:$D$12)-ROW(Sheet2!$D$3)+1)/(Sheet2!$B$3:$B$12=Sheet1!B3),COUNTIF(Sheet1!$B$3:B3,Sheet1!B3))),"")


Sheet2

Book1
ABCDE
1ABC
21SLOTMFT STARTBatch Number
321Thu, 03-Feb-2022100
432Thu, 03-Feb-2022101
543Fri, 04-Feb-2022102
654Sun, 06-Feb-2022103
765Thu, 10-Feb-2022104
871Thu, 03-Mar-2022105
982Fri, 04-Mar-2022106
1093Fri, 04-Mar-2022107
11104Sun, 06-Mar-2022108
12115Thu, 10-Mar-2022109
13
14
15
16
17
18
19
20
21
Sheet2
Click to expand...
1645622975192.png
i get this error message

my formula is ;
=IFERROR(INDEX('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$G$171:$G$195,AGGREGATE(15,6,(ROW('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$G$171:$G$195)-ROW('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$G$171)+1/('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$B$171:$B$195=A116),COUNTIF($A$116:A116,A116))),"")

these are obviously the sheet names not the examples i have given above.

thanks
 
Upvote 0
AlanY said:
will this works for you?

Book1
ABCDEFGH
1SlotModuleStart DateBatch NumberSLOTMFT STARTBatch Number
214Thu, 03-Feb-20221001Thu, 03-Feb-2022100
3 2Thu, 03-Feb-2022101
424Thu, 03-Feb-2022 3Fri, 04-Feb-2022102
5 4Sun, 06-Feb-2022103
637Fri, 04-Feb-2022 5Thu, 10-Feb-2022104
7 1Thu, 03-Mar-2022105
847Sun, 06-Feb-2022 2Fri, 04-Mar-2022106
9 3Fri, 04-Mar-2022107
1057Thu, 10-Feb-2022 4Sun, 06-Mar-2022108
11 5Thu, 10-Mar-2022109
1214Thu, 03-Mar-2022105
13 
1427Fri, 04-Mar-2022 
15 
1634Fri, 04-Mar-2022 
17 
1844Sun, 06-Mar-2022 
19 
2054Thu, 10-Mar-2022 
Sheet1
Cell Formulas
RangeFormula
D2:D20D2=IF(A2=1,INDEX($H$2:$H$11,MATCH(C2,$G$2:G11,0)),"")
Click to expand...
HI Alan,

with this one I get #N/A.

also is there a way to look up multiple values in one? as i have numbers 1-20 each month.

thanks
 
Upvote 0
Pippy79 said:
View attachment 58559i get this error message

my formula is ;
=IFERROR(INDEX('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$G$171:$G$195,AGGREGATE(15,6,(ROW('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$G$171:$G$195)-ROW('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$G$171)+1/('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$B$171:$B$195=A116),COUNTIF($A$116:A116,A116))),"")

these are obviously the sheet names not the examples i have given above.

thanks
Click to expand...
What are both sheet names and also tab names in it

and what the range in which tab you are looking up in second sheet?
 
Upvote 0
Your formula is missing a closing bracket after the +1
Rich (BB code): 
=IFERROR(INDEX('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$G$171:$G$195,AGGREGATE(15,6,(ROW('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$G$171:$G$195)-ROW('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$G$171)+1)/('[V2.0-STE-MFT-BATCH-TRACKING-2021.xlsm]JUL-2021-V2'!$B$171:$B$195=A116),COUNTIF($A$116:A116,A116))),"")
 
Upvote 0
Sufiyan97 said:
What are both sheet names and also tab names in it

and what the range in which tab you are looking up in second sheet?
Click to expand...

for lookup
file name;
V2.0-STE-MFT-BATCH-TRACKING-2021
Sheet / TAB name for batch number;
JUL-2021-V2

for display sheet
File Name:
Slots-STE-2021




Range of batch numbers is in the whole of column G
 
Upvote 0
Pippy79 said:
for lookup
file name;
V2.0-STE-MFT-BATCH-TRACKING-2021
Sheet / TAB name for batch number;
JUL-2021-V2

for display sheet
File Name:
Slots-STE-2021




Range of batch numbers is in the whole of column G
Click to expand...

Have you tried suggestion in post 7 by Fluff?
 
Upvote 0
Sufiyan97 said:
Have you tried suggestion in post 7 by Fluff?
Click to expand...
yes this worked, but when i drag the formula down it doesnt track the correct number all the time.

it will get 4 or so right then a random one will appear.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Calculate difference b/w numbers
Replies
3
Views
123
Peter_SSs
Peter_SSs
M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
205
Peter_SSs
Peter_SSs
S
  • Solved
Reorganising data
Replies
3
Views
302
Dave Patton
D
SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
788
Dave Patton
D
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
227
SaraWitch
SaraWitch

Forum statistics

Threads
1,214,561
Messages
6,120,225
Members
448,951
Latest member
jennlynn

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