Match and VLookup

[murjey]

Hi Team

wondering if you can help. I have 2 worksheets:
Worksheet 1: Lookup table that has 2 columns that provides a water level and a volume - derived from a model

[TABLE="width: 225"]
<tbody>[TR]
[TD]Level[/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD="align: right"]1330[/TD]
[TD="align: right"]14292.8[/TD]
[/TR]
[TR]
[TD="align: right"]1329.9[/TD]
[TD="align: right"]14277.4[/TD]
[/TR]
[TR]
[TD="align: right"]1329.8[/TD]
[TD="align: right"]14262.0[/TD]
[/TR]
[TR]
[TD="align: right"]1329.7[/TD]
[TD="align: right"]14246.4[/TD]
[/TR]
[TR]
[TD="align: right"]1329.6[/TD]
[TD="align: right"]14230.7[/TD]
[/TR]
[TR]
[TD="align: right"]1329.5[/TD]
[TD="align: right"]14214.7[/TD]
[/TR]
[TR]
[TD="align: right"]1329.4[/TD]
[TD="align: right"]14198.4[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2: Data entry sheet where a level is entered and then the second column should input the closest volume (or better yet, proportionate volume if there is not an exact match) based on the lookup sheet:

[TABLE="width: 225"]
<tbody>[TR]
[TD]Level[/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD="align: right"]1329.71[/TD]
[TD="align: right"] ?[/TD]
[/TR]
</tbody>[/TABLE]

I have tried a combination of Match and Vlookup but have not had any success.

Thank you
John

 

[Special-K99]

Try

=INDEX(B1:B7,MATCH(D1,A1:A7,-1)+1,1)

Where D1 is the value to find and table is in A1:B7

 

[Johnny C]

You could try using MATCH with 1 or -1 as the third parameter. from Help ...

1 or omitted
MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.


-1
MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

You could combine them to work out the difference between the volume from the largest level below and the smallest level above the input level.

If you want to interpolate between 2, rather than use VLOOKUP try using OFFSET with the match in it's '1' form assuming the levels in the table are in numeric increasing order.

The value for the volume for the highest level below the exact match is =OFFSET(Sheet1!$B1,MATCH(Sheet2!$A$2, Sheet1!$A2:$A100,1),0)
This finds the highest level lower than the input in Sheet2!$A$2 and gives you the value 1 column to the right (volume).
Then get the volume higher than Sheet2!$A$2
=OFFSET(Sheet1!$B1,MATCH(Sheet2!$A$2, Sheet1!$A2:$A100,1)+1,0)

You can then use
=OFFSET(Sheet1!$A1,MATCH(Sheet2!$A$2, Sheet1!$A2:$A100,1),0) and
=OFFSET(Sheet1!$A1,MATCH(Sheet2!$A$2, Sheet1!$A2:$A100,1)+1,0)
to get you the levels either side of the input level on Sheet2!$A$2
This gives you enough to use linear interpolation using the levels and volumes either side of the input level

Interpolated volume = (Input-lower level)/(higher level - lower level)*(higher volume - lower volume)+lower volume if my rusty memory is correct.

 

[Johnny C]

Sorry I just realised your table is in descending order. You need the match with it's -1 form.

This finds the lowest level higher than the input in Sheet2!$A$2 and gives you the value 1 column to the right
Lowest volume for level higher than input: =OFFSET(Sheet1!$B1,MATCH(Sheet2!$A$2, Sheet1!$A2:$A100,-1)-1,0)
next volume level below: =OFFSET(Sheet1!$B1,MATCH(Sheet2!$A$2, Sheet1!$A2:$A100,-1),0)

Highest level for level lower than input =OFFSET(Sheet1!$A1,MATCH(Sheet2!$A$2, Sheet1!$A2:$A100,-1)-1,0) Next level higher =OFFSET(Sheet1!$A1,MATCH(Sheet2!$A$2, Sheet1!$A2:$A100,-1),0)

have a play and make sure it's working then do the linear interp

 

[murjey]

Great, thank you guys!