Lookup

J

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
755
Office Version
  1. 365
Platform
  1. Windows
Hello

I was wondering could you please help write a lookup up formula, currently im counting if a name has been used, i now would like to return the project number.
Query, AD4 lookup V4, Range AE22:AE104, RESULT 24864

many thanks in advance

JAY - Labour Schedule - JWS - Live Trial 9.xlsm
AQSTUVWXACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
2Week No. 43Week No. 44
3Andrew BallAB0000011000
4Kane BrownKB1000000000
5Scott BrownSB1000000000
6Joe LaceyJL0000000000
7Wayne LongdonWL1000000000
8Jim MorganJM1000000000
9Kevin SharmanKS0000011111
10Luke StayteLS1111100000
11Conner NixonCN0000000000
12Marc NixonMN0000000000
13
14>
1510
16<=Week No. 43Week No. 44
1726/10/20214410
1818-Oct19-Oct20-Oct21-Oct22-Oct25-Oct26-Oct27-Oct28-Oct29-Oct
19Contionious WeekMonTueWedThuFriMonTueWedThuFri
20
21Contract Number:Project Name:Start Date:End Date:Wks:No. DaysMen:101088888886
2224864KERRINGTON Units C&D28/09/2102/11/2153522WL,JM,SB,KB22222KS,AB2KS,AB2KS2KS2
2325027TG Beighton Joseph Ash Bilston29/09/2108/12/21107044444444444KS
2425097JCC COBHAM RETIREMENT VILLAGE 00LS0LS0LS0LS0LS00000
2525143BASTOWS Ashland House, Ashland Place, London, W1U 4AJ27/09/2101/10/210.64.24          
2624899SANDFORDS WEST BROMWICH27/09/2129/09/210.42.84          
Planner1
Cell Formulas
RangeFormula
AD2,AN2AD2=AD16
AD3:AD12AD3=COUNTIFS(Week1!$AE$4:$AK$45,V3)
AF3:AF12AF3=COUNTIFS(Week1!$AE$46:$AK$87,V3)
AH3:AH12AH3=COUNTIFS(Week1!$AE$88:$AK$129,V3)
AJ3:AJ12AJ3=COUNTIFS(Week1!$AE$130:$AK$171,V3)
AL3:AL12AL3=COUNTIFS(Week1!$AE$172:$AK$213,V3)
AN3:AN12AN3=COUNTIFS(Week2!$AE$4:$AK$45,V3)
AP3:AP12AP3=COUNTIFS(Week2!$AE$46:$AK$87,V3)
AR3:AR12AR3=COUNTIFS(Week2!$AE$88:$AK$129,V3)
AT3:AT12AT3=COUNTIFS(Week2!$AE$130:$AK$171,V3)
AV3:AV12AV3=COUNTIFS(Week2!$AE$172:$AK$213,V3)
T3:T12T3=A108
V3:V12V3=P108
AD16,AN16AD16=WEEKNUM(AD18)
T17T17=TODAY()
V17V17=WEEKNUM(T17)
AF18,AH18,AJ18,AL18,AP18,AR18,AT18,AV18AF18=AD18+1
AN18AN18=AL18+3
AD19,AF19,AH19,AJ19,AL19,AN19,AP19,AR19,AT19,AV19AD19=TEXT((AD18), "ddd")
AD21,AF21,AH21,AJ21,AL21,AN21,AP21,AR21,AT21,AV21AD21=SUM(AD22:AD104)+AD106
U22:U26U22=IF(T22="","",T22+W22)
W22:W26W22=V22*7
AD22:AD26AD22=IF(AND($AD$18 >= T22, $AD$18 < U22),X22,IF(AND($AD$18 >= Y22, $AD$18 < Z22),AB22,""))
AF22:AF26AF22=IF(AND($AF$18 >= T22, $AF$18 < U22),X22,IF(AND($AF$18 >= Y22, $AF$18 < Z22),AB22,""))
AH22:AH26AH22=IF(AND($AH$18 >= T22, $AH$18 < U22),X22,IF(AND($AH$18 >= Y22, $AH$18 < Z22),AB22,""))
AJ22:AJ26AJ22=IF(AND($AJ$18 >= T22, $AJ$18 < U22),X22,IF(AND($AJ$18 >= Y22, $AJ$18 < Z22),AB22,""))
AL22:AL26AL22=IF(AND($AL$18 >= T22, $AL$18 < U22),X22,IF(AND($AL$18 >= Y22, $AL$18 < Z22),AB22,""))
AN22:AN26AN22=IF(AND($AN$18 >= T22, $AN$18 < U22),X22,IF(AND($AN$18 >= Y22, $AN$18 < Z22),AB22,""))
AP22:AP26AP22=IF(AND($AP$18 >= T22, $AP$18 < U22),X22,IF(AND($AP$18 >= Y22, $AP$18 < Z22),AB22,""))
AR22:AR26AR22=IF(AND($AR$18 >= T22, $AR$18 < U22),X22,IF(AND($AR$18 >= Y22, $AR$18 < Z22),AB22,""))
AT22:AT26AT22=IF(AND($AT$18 >= T22, $AT$18 < U22),X22,IF(AND($AT$18 >= Y22, $AT$18 < Z22),AB22,""))
AV22:AV26AV22=IF(AND($AV$18 >= T22, $AV$18 < U22),X22,IF(AND($AV$18 >= Y22, $AV$18 < Z22),AB22,""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AN3:AW12Cell Value=1textNO
AN3:AW12Cell Value=0textNO
AD3:AM12Cell Value=1textNO
AD3:AM12Cell Value=0textNO
T22:U104Cellcontains a blank value textNO
T22:U104Cell Value>=$T$17textNO
W22:W104Cell Value=0textNO
AD22:FC104Cell Value=4textNO
AD22:FC104Cell Value=0textNO
AD22:FC104Cell Value=3textNO
AD22:FC104Cell Value=2textNO
AD22:FC104Cell Value=1textNO
AD21,AN21,AX21,AF21,AH21,AJ21,AL21,AP21,AR21,AT21,AV21,AZ21,BB21,BD21,BF21,BH21,BJ21,BL21,BN21,BP21,BR21,BT21,BV21,BX21,BZ21,CB21,CD21,CF21,CH21,CJ21,CL21:FC21Cell Value>$X$17textNO
AD21,AN21,AX21,AF21,AH21,AJ21,AL21,AP21,AR21,AT21,AV21,AZ21,BB21,BD21,BF21,BH21,BJ21,BL21,BN21,BP21,BR21,BT21,BV21,BX21,BZ21,CB21,CD21,CF21,CH21,CJ21,CL21:FC21Cell Value<=$X$15textNO



result:
JAY - Labour Schedule - JWS - Live Trial 9.xlsm
STUVWXACADAEAFAGAHAIAJAKALAM
1
2Week No. 43
3Andrew BallAB00000
4Kane BrownKB248640000
5Scott BrownSB10000
6Joe LaceyJL00000
7Wayne LongdonWL10000
8Jim MorganJM10000
9Kevin SharmanKS00000
10Luke StayteLS11111
11Conner NixonCN00000
Planner1
Cell Formulas
RangeFormula
AD2AD2=AD16
AD3,AD5:AD11AD3=COUNTIFS(Week1!$AE$4:$AK$45,V3)
AF3:AF11AF3=COUNTIFS(Week1!$AE$46:$AK$87,V3)
AH3:AH11AH3=COUNTIFS(Week1!$AE$88:$AK$129,V3)
AJ3:AJ11AJ3=COUNTIFS(Week1!$AE$130:$AK$171,V3)
AL3:AL11AL3=COUNTIFS(Week1!$AE$172:$AK$213,V3)
T3:T11T3=A108
V3:V11V3=P108
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AD3:AM12Cell Value=1textNO
AD3:AM12Cell Value=0textNO
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
j4ymf said:
Query, AD4 lookup V4, Range AE22:AE104, RESULT 24864
Click to expand...
=XLOOKUP(V4,AE$22:AE$104,A$22:A$104)

There are probably free beginner online excel courses that would teach you things this on the first day.
 
Upvote 0
Thank you Jason

It works if i have KB in cell on its own but i have in the cell WL,JM,SB,KB
how do i write that formula

otherwise i get an #NA error
 
Upvote 0
This might work, I haven't tested it. Your sheet is like an explosion in a paint factory, just looking at it is making my eyes bleed!
Excel Formula: 
=XLOOKUP("*"&V4&"*",AE$22:AE$104,A$22:A$104,,2)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
934
paulkc
P
E
  • Solved
Formula not working
Replies
2
Views
334
esmarques
E
P
  • Solved
Keep W-L of card game played over time
Replies
6
Views
755
Preacherman771
P
K
  • Solved
formula for the average of cells across columns with multiple conditions
Replies
4
Views
705
kckliodna
K
D
  • Question Question
Clinching NHL Playoff Spot
Replies
2
Views
75
dotcanada
D

Forum statistics

Threads
1,224,828
Messages
6,181,212
Members
453,023
Latest member
alabaz

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