Help with multiple look ups

[Tonyd789]

Afternoon all

Here we go.......

I am building a spreadsheet at work for managing the amount of racking damage in the workplace. Racking damage is grade as red, amber and green, red meaning racking needs to be immediately offloaded, amber means it turns to red in 4 weeks then needs offloading and green just needs monitoring regularly.

I have the log side of the spreadsheet working no problem, but what i have also included is a birds eye view of the racking system draw with cells that i want to change to the colour of the damage logged with condition formatting.

No here where it gets confusing!
Our aisles are called XA, XB, XC etc etc all the way to XZ
and then our bays range from 01 to 86 eg XA01 XA02 XA03
Then our racking levels range from A to F, eg XA01A XA01B, XA01C, there are a further 2 numbers on the end but thats irrelevant.

So here the tricky bit, i thought about vlookup first for this once i had shortened the location down to the first 4 characters (XA01). However the vlookup will stop at the first XA01 it finds and return the colour value. Now that maybe a green.

What i need is the vlookup to supersede any greens it comes across with amber, and any amber with reds. I'm struggling with this to be fair.

I hope that makes sense!

 

[SpillerBD]

If you gave a numeric value to your "green", "amber" & "red" you could use MIN/MAX functions.

 

[Tonyd789]

Thanks for the reply how would i go about doing that?

 

[Tonyd789]

It's ok i have figured it ok using an array formula.

=MAX(IF($CI$2:$CI$21=D51,$CJ$2:$CJ$21))

However i want to add to the formula to return also the value of the cell 2 rows to the right of where its referencing from! any ideas anyone?