javaandroidvlookup

Is there an Java equivalent for Excel vlookup?


I'm currently coding my first Android app and I need to perform an equivalent of Excel's vlookup. I have a table that will never change and that the user won't see. The user may enter a value that is not exactly in the table in which case the app should use the value equal or smaller and return its equivalent (i.e.: 7 -> 110.3). I will then use the returned value in a formula.

.   A     B      
1   0    110.3
2   5    110.3
3   10   110.7
4   15   111.2
5   20   111.3
6   25   112.3

Solution

  • A TreeMap has methods to find higher or lower keys and entries. Can be used for example like this:

    private static final TreeMap<Integer, Double> table = new TreeMap<Integer, Double>();
    static {
        table.put(0,  110.3);
        table.put(5,  110.3);
        table.put(10, 110.7);
        table.put(15, 110.7);
        table.put(20, 111.2);
        table.put(25, 112.3);
    }
    
    private static double lookup(int value) {
        Entry<Integer, Double> floorEntry = table.floorEntry(value);
        if (floorEntry == null)
            return -1; // or throw sth
        return floorEntry.getValue();
    }
    
    public static void main(String[] args) {
        System.out.println(lookup(7));
    }
    

    110.3