Conditional Formating using Index/Match - Can't seem to get it to work.

[buzzibob]

I've never tried a forum before. So thanks for your help. I'm stumped.

The formula below selects a value from 2 tables. I've tried and just can't get the formula that will work for conditional formatting. I need the formula that goes in the conditional formatting box: "Format values where this formula is true:" I would like to highlight the value in the index area that has been used by the formula.

=IF($C$11="flat to wall",INDEX($J$14:$O$17,MATCH($C$13,$I$14:I17),MATCH($C$12,$J$13:$O$13,1)))*$C$12

Using Excel 2010.

Thanks for your help!!

 

[rilzniak]

What is the formatting you're looking for? What I mean is, what is the result of the second argument in the IF statement?

 

[buzzibob]

Hi Rilzniak. I wish I could load the excel document or photo. I'm not sure if I'm answering this correctly.

Your question: What is the formatting you're looking for?
The grid of numbers is setup 'height of object' (column down left side - $H$14:H17) vs. 'number of objects' (row across top - $I$13:$N$13). Within $J$14:$O$17 it determines the number of minutes per object based on the Height and quantity. I would like the cell selected (number of minutes) by the formula to be highlighted.

The formula yields a total number of minutes to work on a part of certain height and quantity.

Does that answer your question?

 

[buzzibob]

Rilzniak - back at this again. The formula you sent doesnt work because it results in multiple cells being highlighted.

I've tried to pickup instruction from this video: Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples) - YouTube go to minutes 43:10 - 44:00
(Its very close. I don't want to highlight the column and row, just the cell.)
But it doesn't work because vlookup needs an exact value to match in the column. My spreadsheet is not consecutive numbers. I'm using Index command to get the results I need. But can't get it to work in the conditinoal formatting either...

I've tried this: =AND($I22=VLOOKUP($C$22,$I$23:$I$26,1),I$22<=$C$21)
But its out by 1 column.

And this is not right but maybe moving down the right direction: =AND($I22=INDEX($J$23:$O$26,MATCH($C$22,$I$23:I26),I$22=$C$21)


I'm kind of stumped. Maybe another set of eyes need to be on this...

 

[Skybluekid]

Try using Dropbox or Excel jeanie to upload data to the Forum

 

[rilzniak]

Try using Dropbox or Excel jeanie to upload data to the Forum

Agreed. Always easier to see things first hand.

 

[buzzibob]

Ok - Here is the spreadsheet. I've included some descriptions for what i'm trying to do in the document.

https://dl.dropboxusercontent.com/u/65976315/mrexcel.xlsx

Thanks for your help!!