S samlim88 New Member Jan 10, 2010 #1 I am having trouble returning the next highest value using HLOOKUP. For example: My table array (data) = 10, 20, 30, 40, 50 If my lookup value is 34, HLookup returns 30. I would like it to return 40 (round up instead of down). Any help would be greatly appreciated
I am having trouble returning the next highest value using HLOOKUP. For example: My table array (data) = 10, 20, 30, 40, 50 If my lookup value is 34, HLookup returns 30. I would like it to return 40 (round up instead of down). Any help would be greatly appreciated
oldchippy Active Member Jan 10, 2010 #2 Hi, Here's one way assuming your data is in A1:E1, your lookup value in G1=34 then this in H1 =INDEX(A1:E1,IF(ISNUMBER(MATCH(G1,A1:E1,0)),MATCH(G1,A1:E1,0),1+MATCH(G1,A1:E1,1))) Result=40
Hi, Here's one way assuming your data is in A1:E1, your lookup value in G1=34 then this in H1 =INDEX(A1:E1,IF(ISNUMBER(MATCH(G1,A1:E1,0)),MATCH(G1,A1:E1,0),1+MATCH(G1,A1:E1,1))) Result=40