Conditional Formatting with Vlookup and IF Funtions over two Tables

mk90

New Member
Joined
Apr 6, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

What I am trying to achieve is, when a line in table 2 has
=IF(AND(Orders[Post Pre]="M",Orders[Supplier]="AWS - EXT",Orders[Status]="Completed")
to then look up the Job# in B and find that number in Table 1 and Colour a specific cell (P in table 1, to be linked to Orders[Supplier]="AWS - EXT")

Do I need to use Vlookup after IF(AND), or do I need to use Index/Match?
either of these I cant seem to figure out with conditional formatting.

Thanks
(sorry if this is so confusing!)


See below table photos, I hope it paints the picture of what I would like to achieve...
Sheet 2)
1617842710634.png


Sheet 1)

1617843035349.png
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

You circled Column J & "M", is that Also a requirement?
And for your sample, you want "Y" in P8 highlighted?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Use one of these for your CF formula.

Q21 includes the J Column requirement for M
R21 excludes J Column
Q21:R23 shown for reference purposes Only, you Don't need them.

Change/Adjust Cell Range/References to suit your data:

Book3.xlsx
BCDEFGHIJKLMNOPQR
583000
683000XYZYES
782000ABCCompleted
881000
981000
1081000AWS - EXTCompletedM
1183000
1283000
1383000
1483000DEFCompleted
1583000
1683000
17
18
19
20
2183000Y00
2282000Y00
2381000Y11
24With JWithout J
Sheet901
Cell Formulas
RangeFormula
Q21:Q23Q21=COUNTIFS(B$5:B$20,B21,D$5:D$20,"AWS - EXT",I$5:I$20,"Completed",J$5:J$20,"M")
R21:R23R21=COUNTIFS(B$5:B$20,B21,D$5:D$20,"AWS - EXT",I$5:I$20,"Completed")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
P21:P23Expression=COUNTIFS(B$5:B$20,B21,D$5:D$20,"AWS - EXT",I$5:I$20,"Completed",J$5:J$20,"M")textNO
 

mk90

New Member
Joined
Apr 6, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you for your fast reply.

I see what you have done, although I cant get it to work over 2 sheets.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You should be able to adjust the formula with your Sheet Names and Cell Ranges/References for it to work.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,941
Members
416,953
Latest member
prakashkumar

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
Top