lookup row and copy to another sheet

B

bdt

Board Regular
Joined
Oct 3, 2024
Messages
56
Office Version
  1. 2019
Platform
  1. Windows
I have a table in sheet "forcast_planning" where I need to select the appropriate row where column B is lowest and where column A equals to "01", column G equals to "Motor". This row needs to be copied to row 1 in Sheet1 of the same workbook.
As a novice Excel user I've tried using VLookup with no luck
Any help much appreciated. Thanks.

forcast_planning.xlsx
ABCDEFGHIJKLMNOP
1013015298631/01/202311001-4B1269722Motor24000016962871.92207E-0533681804252107964.939.20281274.4196719.9248
2013015298831/01/202311001-3B2065722Motor24000016962871.92207E-0533681804252107964.939.20281274.4196719.9248
3013015298431/01/202311001-1B1205721Motor24000016962871.92207E-0533681804252107964.939.20281274.4196718.9248
4013015298931/01/202311001-2B2388721Motor24000016962871.92207E-0533681804252107964.939.20281274.4196718.9248
5013015298130/01/202313001-2B1631728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
6013015298531/01/202313001-4B1258728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
7013015298731/01/202313001-3B1643728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
8013015297930/01/202313001-1B1242728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
9013015297830/01/202314001-1B1224726Motor24000016962871.92207E-0533681804252107964.939.20281274.4196723.9248
10013015298230/01/202314001-2B2003726Motor24000016962871.92207E-0533681804252107964.939.20281274.4196723.9248
11013015298330/01/202314001-3B2177726Motor24000016962871.92207E-0533681804252107964.939.20281274.4196723.9248
12013015298030/01/202314001-4B1264726Motor24000016962871.92207E-0533681804252107964.939.20281274.4196723.9248
13013015320620/03/202312001-4B3218703Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072700.4835
14013015320720/03/202312001-3B3227702Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072699.4835
15013015320520/03/202312001-2B3210703Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072700.4835
16013015320420/03/202312001-1B3201703Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072700.4835
17023015299101/02/202313002-1B1259727Motor24000016962871.92207E-0533681804252107964.939.20281274.4196724.9248
18023015299601/02/202311002-3B2164722Motor24000016962871.92207E-0533681804252107964.939.20281274.4196719.9248
19023015299501/02/202313002-2B1645727Motor24000016962871.92207E-0533681804252107964.939.20281274.4196724.9248
20023015299401/02/202313002-3B1611727Motor24000016962871.92207E-0533681804252107964.939.20281274.4196724.9248
21023015299201/02/202313002-4B1275727Motor24000016962871.92207E-0533681804252107964.939.20281274.4196724.9248
22023015299001/02/202311002-1B1243721Motor24000016962871.92207E-0533681804252107964.939.20281274.4196718.9248
23023015299701/02/202311002-2B2189721Motor24000016962871.92207E-0533681804252107964.939.20281274.4196718.9248
24023015299301/02/202311002-4B1454722Motor24000016962871.92207E-0533681804252107964.939.20281274.4196719.9248
25023015300502/02/202314002-3B2178728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
26023015300402/02/202314002-2B1287729Motor24000016962871.92207E-0533681804252107964.939.20281274.4196726.9248
27023015300202/02/202314002-4B1253728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
28023015300302/02/202314002-1B1266728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
29023015321021/03/202312002-4B3359701Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072698.4835
30023015321121/03/202312002-3B3360701Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072698.4835
31023015320821/03/202312002-2B3224701Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072698.4835
32023015320921/03/202312002-1B3340701Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072698.4835
33033015227823/06/202213003-4B1227735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
34033015229123/06/202213003-1B9079735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
35033015228423/06/202213003-2B1762735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
36033015227923/06/202211003-4B1385732Motor24000014910861.92207E-0533681696595205508.910.2408271.68571728.05
37033015228723/06/202211003-3B2073732Motor24000014910861.92207E-0533681696595205508.910.2408271.68571728.05
38033015228623/06/202211003-2B2049733Motor24000014910861.92207E-0533681696595205508.910.2408271.68571729.05
39033015228023/06/202211003-1B1396733Motor24000014910861.92207E-0533681696595205508.910.2408271.68571729.05
40033015228323/06/202213003-3B1624735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
41033015228223/06/202214003-1B1468735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
42033015228523/06/202214003-2B2042734Motor24000014910861.92207E-0533681696595205508.910.2408271.68571730.05
43033015228923/06/202214003-3B2249735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
44033015227623/06/202214003-4B1059733Motor24000014910861.92207E-0533681696595205508.910.2408271.68571729.05
45033015410101/08/202312003-3B3118694Trailer15000016757472.89205E-053368169659520847.9338.34681268.4277693.3971
46033015411301/08/202312003-4B3455695Trailer15000016757472.89205E-053368169659520847.9338.34681268.4277694.3971
47033015410401/08/202312003-2B3187694Trailer15000016757472.89205E-053368169659520847.9338.34681268.4277693.3971
48033015410501/08/202312003-1B3198694Trailer15000016757472.89205E-053368169659520847.9338.34681268.4277693.3971
49043015228823/06/202211004-2B2155733Motor24000014910861.92207E-0533681696595205508.910.2408271.68571729.05
50043015229023/06/202211004-3B2267734Motor24000014910861.92207E-0533681696595205508.910.2408271.68571730.05
51043015228123/06/202211004-1B1423733Motor24000014910861.92207E-0533681696595205508.910.2408271.68571729.05
52043015229724/06/202213004-1B1397735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
53043015227723/06/202211004-4B1146733Motor24000014910861.92207E-0533681696595205508.910.2408271.68571729.05
54043015229924/06/202213004-2B1695735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
55043015229824/06/202213004-3B1688735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
56043015229624/06/202213004-4B1388734Motor24000014910861.92207E-0533681696595205508.910.2408271.68571730.05
57043015229424/06/202214004-4B1293735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
58043015229524/06/202214004-1B1345735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
59043015230024/06/202214004-3B214699735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
60043015230124/06/202214004-2B2369735Motor24000014910861.92207E-0533681696595205508.910.2408271.68571731.05
61043015411402/08/202312004-4B3328694Trailer15000016757472.89205E-053368169659520847.9338.34681268.4277693.3971
62043015411502/08/202312004-3B3453693Trailer15000016757472.89205E-053368169659520847.9338.34681268.4277692.3971
63043015409401/08/202312004-2B3020693Trailer15000016757472.89205E-053368169659520847.9338.34681268.4277692.3971
64043015410001/08/202312004-1B3114693Trailer15000016757472.89205E-053368169659520847.9338.34681268.4277692.3971
65053015435012/09/202311005-4B1333725Motor24000018739611.92207E-05336818749901029.24370.95331496.6732724.9802
66053015435612/09/202311005-3B2248726Motor24000018739611.92207E-05336818749901029.24370.95331496.6732725.9802
67053015435712/09/202311005-2B2383726Motor24000018739611.92207E-05336818749901029.24370.95331496.6732725.9802
68053015434712/09/202311005-1B1025725Motor24000018739611.92207E-05336818749901029.24370.95331496.6732724.9802
69053015435112/09/202313005-1B1337728Motor24000018739611.92207E-05336818749901029.24370.95331496.6732727.9802
70053015435312/09/202313005-2B1638727Motor24000018739611.92207E-05336818749901029.24370.95331496.6732726.9802
71053015435212/09/202313005-3B1633728Motor24000018739611.92207E-05336818749901029.24370.95331496.6732727.9802
72053015434812/09/202313005-4B1046728Motor24000018739611.92207E-05336818749901029.24370.95331496.6732727.9802
73053015434912/09/202314005-1B1229726Motor24000018739611.92207E-05336818749901029.24370.95331496.6732725.9802
74053015435512/09/202314005-2B2174727Motor24000018739611.92207E-05336818749901029.24370.95331496.6732726.9802
75053015435412/09/202314005-3B2159728Motor24000018739611.92207E-05336818749901029.24370.95331496.6732727.9802
76053015435812/09/202314005-4B9029728Motor24000018739611.92207E-05336818749901029.24370.95331496.6732727.9802
77053015425729/08/202312005-4B3432739Trailer15000018668882.89205E-0533681874990810242.13124294.9186738.7657
78053015425229/08/202312005-3B3249739Trailer15000018668882.89205E-0533681874990810242.13124294.9186738.7657
79053015425629/08/202312005-2B3354739Trailer15000018668882.89205E-0533681874990810242.13124294.9186738.7657
80053015425529/08/202312005-1B3352739Trailer15000018668882.89205E-0533681874990810242.13124294.9186738.7657
81063015107420/12/202111006-2B2172732Motor24000016373771.92207E-0533681874990237613.20.7086574.9606727.4329
82063015106820/12/202111006-1B124799732Motor24000016373771.92207E-0533681874990237613.20.7086574.9606727.4329
83063015107220/12/202113006-2B1622734Motor24000016373771.92207E-0533681874990237613.20.7086574.9606729.4329
84063015106920/12/202113006-1B1249734Motor24000016373771.92207E-0533681874990237613.20.7086574.9606729.4329
85063015108121/12/202113006-3B1630733Motor24000016373771.92207E-0533681874990237613.20.7086574.9606728.4329
86063015108021/12/202113006-4B1407733Motor24000016373771.92207E-0533681874990237613.20.7086574.9606728.4329
87063015107520/12/202111006-3B2247733Motor24000016373771.92207E-0533681874990237613.20.7086574.9606728.4329
88063015106720/12/202111006-4B1204733Motor24000016373771.92207E-0533681874990237613.20.7086574.9606728.4329
89063015108221/12/202114006-3B2168732Motor24000016373771.92207E-0533681874990237613.20.7086574.9606727.4329
90063015107921/12/202114006-1B1158732Motor24000016373771.92207E-0533681874990237613.20.7086574.9606727.4329
91063015108321/12/202114006-2B2170732Motor24000016373771.92207E-0533681874990237613.20.7086574.9606727.4329
92063015107821/12/202114006-4B1089732Motor24000016373771.92207E-0533681874990237613.20.7086574.9606727.4329
93063015424729/08/202312006-2B3139739Trailer15000018668882.89205E-0533681874990810242.13124294.9186738.7657
94063015424229/08/202312006-1B3055739Trailer15000018668882.89205E-0533681874990810242.13124294.9186738.7657
95063015424629/08/202312006-3B3122739Trailer15000018668882.89205E-0533681874990810242.13124294.9186738.7657
96063015425429/08/202312006-4B3261739Trailer15000018668882.89205E-0533681874990810242.13124294.9186738.7657
forcast_planning


forcast_planning.xlsx
ABCDEFGHIJKLMNOP
101
Sheet1
 
Sort by date Sort by votes
Hi @bdt

Since you are on MSO 2019 the functions are limited.
Assuming the following:
  • values in Column A are formatted as a number
  • values in Column B are unique
I tried to find a suitable formula for MSO 2019 explicitly and came up with the following:

MrExcel_2025-03.xlsm
ABCDEFGHIJKLMNOP
11301529784495614001-1B1224726Motor24000016962871.92E-0533681804252107964.939.20281274.4196723.9248
Sheet1
Cell Formulas
RangeFormula
A1:P1A1= INDEX(forcast_planning!$A:$P,MATCH(MINIFS(forcast_planning!$B:$B, forcast_planning!$A:$A, "=1", forcast_planning!$G:$G, "Motor"),forcast_planning!$B:$B,0),0)
Dynamic array formulas.


Let me know if that's what you're after.
 
Upvote 0
Solution
Peter, many thanks for your reply.
Your result is exactly what I was after. Unfortunately I only get 01 in A1 on sheet1 when your formula is pasted into A1 of sheet1. I don't seem to be able to return the entire row, ie. columns A to P. I must be missing something simple. I have formatted column A to number and column B is formatted to general and the numbers in B are unique.
Many thanks, Ben.

forcast_planning.xlsx
ABCDEFGHIJKLMNOP
113015298631/01/202311001-4B1269722Motor24000016962871.92207E-0533681804252107964.939.20281274.4196719.9248
213015298831/01/202311001-3B2065722Motor24000016962871.92207E-0533681804252107964.939.20281274.4196719.9248
313015298431/01/202311001-1B1205721Motor24000016962871.92207E-0533681804252107964.939.20281274.4196718.9248
413015298931/01/202311001-2B2388721Motor24000016962871.92207E-0533681804252107964.939.20281274.4196718.9248
513015298130/01/202313001-2B1631728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
613015298531/01/202313001-4B1258728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
713015298731/01/202313001-3B1643728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
813015297930/01/202313001-1B1242728Motor24000016962871.92207E-0533681804252107964.939.20281274.4196725.9248
913015297830/01/202314001-1B1224726Motor24000016962871.92207E-0533681804252107964.939.20281274.4196723.9248
1013015298230/01/202314001-2B2003726Motor24000016962871.92207E-0533681804252107964.939.20281274.4196723.9248
1113015298330/01/202314001-3B2177726Motor24000016962871.92207E-0533681804252107964.939.20281274.4196723.9248
1213015298030/01/202314001-4B1264726Motor24000016962871.92207E-0533681804252107964.939.20281274.4196723.9248
1313015320620/03/202312001-4B3218703Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072700.4835
1413015320720/03/202312001-3B3227702Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072699.4835
1513015320520/03/202312001-2B3210703Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072700.4835
1613015320420/03/202312001-1B3201703Trailer15000017172372.89205E-053368180425287014.9518.70103130.9072700.4835
1723015299101/02/202313002-1B1259727Motor24000016962871.92207E-0533681804252107964.939.20281274.4196724.9248
1823015299601/02/202311002-3B2164722Motor24000016962871.92207E-0533681804252107964.939.20281274.4196719.9248
forcast_planning


forcast_planning.xlsx
ABCDEFGHIJKLMNOP
101
Sheet1
Cell Formulas
RangeFormula
A1A1= INDEX(forcast_planning!$A:$P,MATCH(MINIFS(forcast_planning!$B:$B, forcast_planning!$A:$A, "=1", forcast_planning!$G:$G, "Motor"),forcast_planning!$B:$B,0),0)
Named Ranges
NameRefers ToCells
forcast_planning=forcast_planning!$A$1:$Q$1504A1
 
Upvote 0
Peter,
Issue now solved. I hadn't realised I'd have to copy and paste the formula into all columns A to P.
Many thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Multiple Fault Code Mapping Based on Time Slot Ranges
2
Replies
16
Views
155
Surendra Bhilare
S
V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
207
vancher19
V
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
585
dshtesy
D
C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
351
CaptainGravyBum
C
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
285
sanrama
S

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