In Excel => How to get the closest value not greater than the lookup value from
ID: 3565334 • Letter: I
Question
In Excel => How to get the closest value not greater than the lookup value from a specific column?
Dear Microsoft Community,
I would like to ask how to get the closest/highest value but not greater than the lookup value from a range and from a specific column? This is my data as follows.
I have tried vlookup and if function but is nbot giving the correct value.
In the case above it should be returning the value 10,000 froom column ME2/S2 not 14,583. It is returning the value greater than the lookup value insteead.
Please help me in this. Thankss.
A B C D E F G H 1 ME1/S1 ME2/S2 ME3/S3 ME4/S4 Column (Variable) Lookup Value (variable) Result 2 1.00 1.00 1.00 1.00 ME2/S2 14,479.51 3 3,125.00 4,167.00 5,208.00 6,250.00 4 3,542.00 4,583.00 5,625.00 6,667.00 5 4,375.00 5,417.00 6,458.00 7,500.00 6 6,042.00 7,083.00 8,125.00 9,167.00 7 8,958.00 10,000.00 11,042.00 12,083.00 8 13,542.00 14,583.00 15,625.00 16,667.00 9 23,958.00 25,000.00 26,042.00 27,083.00Explanation / Answer
Hi,,,,
In cell H2, enter this formula
=VLOOKUP(G2,INDIRECT(ADDRESS(2,MATCH(F2,$A$1:$D$1,0))&":"&ADDRESS(9,MATCH(F2,$A$1:$D$1,0))),1,1)
Hope tthis helpss....