VLOOKUP behavior
Posted by George Crowley on December 26, 2001 8:51 AM
I have a lookup table that increments in 0.5 units.
What I want to do is to get the average of 2 cells, and do a vlookup to get a value. My problem is that, my calculation requires that I round up to the next 0.5 unit and get the data from the table.
I have a fairly nasty looking nested IF statement that does not work if the value is less than X.5
Example:
Table
0.5 06
1.0 11
1.5 22
2.0 33
2.5 44
2.0 55
If I have 1.7 I want to get the 33 value
if I have 1.5 I want to get the 22 value
If I have 1.3 I want to get the 22 value
If I have 0.99 I want to get the 1.0 value
Thanks
Posted by Jack in UK on December 26, 2001 9:16 AM
George --
Can you post your formula please?
Posted by George on December 26, 2001 9:18 AM
I figured it out:
I used the MOD(average(C5:C10,0.5) and an IF statement with a couple VLOOKUPS.
THanks
Posted by Jacob on December 26, 2001 9:18 AM
Hi
If your table is sorted in ascending order then the easy solution would be to have the fourth value in your vlookup formula as True. This way Vlookup will find the closest match. If this wont work reply and I think of something else.
Jacob
Posted by George Crowley on December 26, 2001 9:19 AM
Here is what I ended up with:
=IF(A24<>2,"N/A",IF(MOD(AVERAGE(E21:E22),0.5)>0,VLOOKUP(AVERAGE(E21:E22)+0.5,dewpoint,2),VLOOKUP(AVERAGE(E21:E22),dewpoint,2)))
Posted by Jack in UK on December 26, 2001 10:50 AM
George --
Can you post your formula please?
Posted by Aladýn Akyurek on December 27, 2001 8:08 AM
George --
As I understand it, you have a table called DEWPOINT and you want to pre-process the lookup value that you feed to a VLOOKUP formula:
What follows will do what I take to be what you want:
=VLOOKUP(IF(A1-INT(A1)<=0.5,ROUND(A1/0.5,0)*0.5,ROUND(A1,0)),DEWPOINT,2,0)
Hope thýs helps.
Aladin
===========
