Conditional formatting using VLOOKUP

M

mattadams84

Board Regular
Joined
Oct 30, 2016
Messages
54
Hello,

I am trying to set some conditional formatting on a sheet but i think i need to use the VLOOKUP formula.

On SHEET1 i have rows of 4 digit identifiers in column A. Then in column H, I and J there are single numbers.

On SHEET 2, where i want the conditional formatting ,there is also some of the 4 digit identifiers (from SHEET 1) in Column A and then loads of other data in the other columns.

I want to, for example, format the cells in column C on SHEET 2 to :

if the number in this cell is less than the number on SHEET 1 in column H on the ROW where there is the same unique 4 digit identifier, then format the cell red...

I am pretty sure i have to "USE A FORMULA" in conditional formatting, and use some sort of VLOOKUP, but i cant seem to get it to work. Here is what i tried :

Excel Formula: 
<=VLOOKUP($A2,'SHEET1'!$A$2:$A$5135,COLUMN(H$2),FALSE)

Any help appreciated
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
If the 4 digit identifiers only exist once on sheet1 try
Excel Formula: 
=C2<=SUMIFS(Sheet1!H:H,Sheet1!A:A,A2)
 
Upvote 0
Solution
On sheet 1 the identifiers only exist once, on sheet two there are multiple instances, but it always needs to look up the value from sheet 1
 
Upvote 0
In that case did you try the formula I suggested?
 
Upvote 0
That does not seem to work either, in my case I changed the formula to this :

="CM2<=SUMIFS('DATA LIST'!$H2:$H,'DATA LIST'!$A2:$A,$A2)"
 
Upvote 0
You can either use the entire column, or you have to specify both a start & end row.
 
Upvote 0
Here is an example of the data that i would like to condtionally format

6639Australia A-League602Melbourne City FC38201826141212
6639Australia A-League671683Western United2112914687
6639Australia A-League601Adelaide United2815132412124
6639Australia A-League689390Macarthur261313269170
6639Australia A-League607Melbourne Victory FC20146171073
6639Australia A-League606Sydney FC211110211290
6639Australia A-League609Wellington Phoenix1916322913-3
6639Australia A-League608Western Sydney Wanderers1881024816-6
6639Australia A-League603Central Coast Mariners2012822913-2
6639Australia A-League605Newcastle Jets FC241311281117-4
6639Australia A-League604Brisbane Roar1410421516-7
6639Australia A-League600Perth Glory FC12481688-4
6007Austria 2. Liga592Austria Lustenau4625212181325
6007Austria 2. Liga589Floridsdorfer AC332013128421
6007Austria 2. Liga593Blau-Weiß Linz321715189914
6007Austria 2. Liga594Liefering39211825141114
6007Austria 2. Liga3101Amstetten4222202161521
6007Austria 2. Liga3104Lafnitz3624123119125
6007Austria 2. Liga3410Grazer AK3215172612146
6007Austria 2. Liga588Wacker Innsbruck3216162512137
6007Austria 2. Liga580St. Pölten281992313105
6007Austria 2. Liga3077Rapid Wien II28919391722-11
6007Austria 2. Liga596Kapfenberger SV231112381424-15
6007Austria 2. Liga3062Dornbirn261313451926-19
6007Austria 2. Liga3086Austria Wien II21714331815-12
6007Austria 2. Liga590Horn23914391623-16
6007Austria 2. Liga3087Vorwärts Steyr211110401921-19
6007Austria 2. Liga3094SPG FC Pasching-LASK Juniors24168502723-26
6008Austria Bundesliga595LASK Linz3418162912175
6008Austria Bundesliga100Salzburg513417135838
6008Austria Bundesliga582Sturm Graz46232333161713
6008Austria Bundesliga578Admira271314311417-4
6008Austria Bundesliga581Wolfsberger AC3520153414201
6008Austria Bundesliga599Hartberg291415351817-6
6008Austria Bundesliga577Rheindorf Altach1055382018-28
6008Austria Bundesliga579Austria Wien3318152412129
6008Austria Bundesliga584Ried312110431528-12
6008Austria Bundesliga583Rapid Wien3821173214186
6008Austria Bundesliga591Wattens301713481632-18
6008Austria Bundesliga598Austria Klagenfurt321814361818-4
6010Belgium First Division B534KVC Westerlo4121202213919
6010Belgium First Division B669393RWDM3419152716117
6010Belgium First Division B526Waasland-Beveren4319243520158
6010Belgium First Division B2578KMSK Deinze4022183417176
6010Belgium First Division B538Royal Excel Mouscron281414331320-5
6010Belgium First Division B669390Lierse Kempenzonen331617381820-5
6010Belgium First Division B2573Lommel United311714351520-4
6010Belgium First Division B2581Excelsior Virton19811452124-26
6079Belgium Pro League2572Union Saint-Gilloise69264325131244
6079Belgium Pro League95Club Brugge64313335152029
6079Belgium Pro League61RSC Anderlecht65372833191432
6079Belgium Pro League1048Royal Antwerp FC52282437181915
6079Belgium Pro League525KAA Gent48311728161220
6079Belgium Pro League529Sporting Charleroi4716314216265
6079Belgium Pro League537KV Mechelen533320562234-3
6079Belgium Pro League533KRC Genk58342444182614
6079Belgium Pro League2567Cercle Brugge4621253818208
6079Belgium Pro League532Sint-Truiden342014382117-4
6079Belgium Pro League690OH Leuven462521512427-5
6079Belgium Pro League536KV Kortrijk392118412021-2
6079Belgium Pro League527Standard Liège301416452322-15
6079Belgium Pro League535Zulte-Waregem422319633033-21
6079Belgium Pro League531KV Oostende291712583523-29
6079Belgium Pro League530AS Eupen361719532627-17

Here is the list of data on sheet 1

663920212022AustraliaA-League2021/22Australia A-League12489
45412020AustraliaBrisbane Premier League2020Australia Brisbane Premier League
55872021AustraliaBrisbane Premier League2021Australia Brisbane Premier League
56522021AustraliaBrisbane Reserves Premier2021Australia Brisbane Reserves
60072021Austria2. Liga2021Austria 2. Liga10367
60082019AustriaBundesliga2019Austria Bundesliga1551011
36332020BelizeCapital Territory NPL2020Belize Capital Territory NPL
60102021BelgiumFirst Division B2021Belgium First Division B8356
60792022BelgiumPro League 2022Belgium Pro League1651011
 
Upvote 0
Try
Excel Formula: 
=CM2<=SUMIFS('DATA LIST'!$H$2:$H$10000,'DATA LIST'!$A$2:$A$10000,$A2)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Using Macro/VLOOKUP to return format of cell
Replies
9
Views
227
mumps
mumps
L
  • Solved
Conditional Formatting in Excel Table and Ignore Blanks Not Working
Replies
3
Views
354
Joe4
Joe4
J
  • Question
Conditional Formatting.. The bane of my Excel Existence
Replies
2
Views
137
RoryA
RoryA
D
  • Question
Multiple conditions
Replies
8
Views
264
awoohaw
awoohaw
D
  • Question
conditional formatting
Replies
1
Views
112
etaf
etaf

Forum statistics

Threads
1,215,831
Messages
6,127,138
Members
449,361
Latest member
VBquery757

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