conditional format with a vlookup and if statement

Question

I need help please.

I have a spreadsheet, on one tab column D has the name of a country and column K has a charge. There are 14 countries altogether.

On tab 2 I have a table with each country and a minimum and maximum cost against each (column T is country, U is minimum and V is maximum).

I need my conditional format to find the country for that row from the table in tab 2, I then however need it to highlight the cost in column k from tab 1 in blue if it goes below the value of min (column T) in tab 2 or in red if K is higher than the max.

I have tried a couple of times now but each time I seem to miss something.

Any help is massively appreciated.

Have you tried by using a formula in the conditional formatting? And if so, can you share that formula? — Alex de Jong
– Alex de Jong, Commented Mar 4, 2019 at 14:41
=IF(VLOOKUP(D2,Europa!$T$9:$V$22,2,FALSE),<'Export 2019'!$K$3=RED) — L Rogers
– L Rogers, Commented Mar 4, 2019 at 15:07
I have 14 countries each with a min and maximum in the Europa sheet. Each row in the Export sheet will contain at least one of these countries in. I am trying to say if column K in export is less than the minimum in Europa then highlight red. To be honest I am just trying to figure it out for the min before I include the AND for max. — L Rogers
– L Rogers, Commented Mar 4, 2019 at 15:09
I am doing this in the conditional format - new rule - use a formuala — L Rogers
– L Rogers, Commented Mar 4, 2019 at 15:10

Alex de Jong · Accepted Answer · 2019-03-05 09:13:31Z

1

I think your formula is almost correct. How about this:

=IF(VLOOKUP(D2,Europa!$T$9:$V$22,2,False)>K2,1,0)

The output is either 1(true) or 0(false). In this case the cell changes color if K2 is smaller than column 2 of your VLOOKUP range.

edited Mar 5, 2019 at 9:13

answered Mar 4, 2019 at 15:12

Alex de Jong

1,2671 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

L Rogers Over a year ago

Can't seem to get this to work either, thanks for the help though. Will keep at it.

L Rogers Over a year ago

I have purposefully made my first row K price less than the minimum in the Europa tab and I changed > to < but it does not seem to be changing. Even tried it just as a normal formula in the next column and changed 1 & 0 to "High" & "Low" but just get NA

Alex de Jong Over a year ago

Try changing back the < to > and leave 1 and 0. If K2 is smaller than the Europatab, then Europatab > K2 is correct in this formula. It triggers when the formula is true (or 1)

L Rogers Over a year ago

Thank you shall give it a shot tomorrow.

Alex de Jong Over a year ago

Glad it works. I changed my answer so it says $T$9 now, in case of future references.

|

Collectives™ on Stack Overflow

conditional format with a vlookup and if statement

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related