in the example below I want to be able to type the width and drop in the two relevant fields and have the price value returned in the Price box. I have tried various combinations of VLOOKUP, INDEX and MATCH with some success provided the width and height value match exactly. But I can't seem to get it working correctly when the width and drop entries are arbitrary. I need both these values to round up to the next value in the table if they don't match exactly what is in the table. Can anyone help with the correct formula please?
Answer
MATCH will return where the value greater than or equal and less then the next. You want the other way around. You will need an array formula:
=INDEX($B$2:$P$22,MATCH(MIN(IF($A$2:$A$22>=$T$2,$A$2:$A$22)),$A$2:$A$22,0),MATCH(MIN(IF($B$1:$P$1>=$S$2,$B$1:$P$1)),$B$1:$P$1,0))
Being an array formula it must be confirmed with Ctrl-Shift-Enter, Instead of Enter when exiting edit mode.
It will now find the location where each of the two values are greater than or equal to the values provided.
No comments:
Post a Comment