PDA

View Full Version : Solved: Possible Vlookup or Index/Match formula help



Glaswegian
08-31-2008, 06:45 AM
Hi all

My knowledge of formulae is not very good so I'd be grateful for some help on this one.

Users will be required to input 2 values to a worksheet. Using these values, I will then do some calculations. The first one is a temperature reading - it will be a straightforward value. The second is a density reading - while it will also be a straightforward value, I'll need to use 2 of the values in a table. A brief sample of the table is here

http://i26.photobucket.com/albums/c109/TheGlaswegian/XLsample.jpg
The temperature figures are in column A.

For example, let's say the temperature reading is 23.5 and the density reading is 0.823. I would then go to row 2 (from the temperature reading) and I would need to find the 2 values that include the density reading - in this case 0.8224(col D) and 0.8324 (col E). Once I have those 2 values I will then do some necessary arithmetic in the background.

What formula would I need to be able to extract those two values, based on the user input? Possibly Index/Match?

Thanks.

Bob Phillips
08-31-2008, 07:09 AM
Lower: =LOOKUP(B15,INDEX(B1:H3,MATCH(A15,A1:A3,0),0))
Upper: =INDEX(B1:H3,MATCH(A15,A1:A3,0),MATCH(B15,INDEX(B1:H3,MATCH(A15,A1:A3,0),0) ,1)+1)

the latter formula is an array formula.

This will fail if the density is less than or greater than the outer values.

Glaswegian
08-31-2008, 07:32 AM
Hi xld

Many thanks - the first one works a treat but I can't get the second one to work and I have entered it as an array formula. Likely something stupid that I'm doing...

Glaswegian
08-31-2008, 07:34 AM
Edit that - got it working fine - thanks (as I said, just my stupidity...)