price List lookup

[squiggler47]

Cant figure right now, brain is fried neighbours had a party until 4am last night, had no slep but :-

I have a price list Width/Drop

1000 1300 1600 1900 2200 2500 2800 3100 3400 3700 4000 4300 4600 4900 5200

850
1150
1450
1750
2050
2350
2650
2950
3250
3550
3850

I need to index given :-

My ranges are named Width and drop and List

Rules are anything above largest drop/width must return 0
anything below lowest width/drop will be the lowest listed
anything inbetween will choose the value >= to the value

lookup is returning the closest, and my other formula fell foul of nesting, i am working in 2007 but it is targeted at XP/2002

TIA, i would figure it but no sleep is catching up on me!

[DonkeyOte]

[removed in lieu of uploaded sample file]

[DonkeyOte]

Based on your sample file:

E19:
=IF(OR($C19>$Q$4,$D19>$B$15),0,INDEX($C$5:$Q$15,IF($D19<$B$5,1,MATCH($D19,$B$5:$B$15)+ISNA(MATCH($D19,$B$5:$B$15,0))),IF($C19<$C$4,1,MATCH($C19,$C$4:$Q$4)+ISNA(MATCH($C19,$C$4:$Q$4,0)))))

[squiggler47]

Sorry slurped some coffee and I came up with something

I was overthinking! And I gave my example wrong i got the width and height screwed up, told you I was half asleep!

=IF(ISERROR(INDEX(List,COUNT(Drop)-COUNTIF(Drop,">=" &D19)+1,COUNT(Width)-COUNTIF(Width,">=" &C19)+1)),"",INDEX(List,COUNT(Drop)-COUNTIF(Drop,">=" &D19)+1,COUNT(Width)-COUNTIF(Width,">=" &C19)+1)

[squiggler47]

Thanks for your help though its apreciated !

what would make it perfect is if you could give me a sheet that will gag the neighbours so I wouldnt have to ask!

Regards

Darren

[NBVC]

Another sample attached... Uses named ranges, Drop, Width and Table