round up

i have table were the values in Colum b,the first 20 increase by 5000 and the 10 000 and then 50 000 if i hava value of 116000 then the price in Colum of 120 000 must be use how do i write ...

test 2.xlsx33 KB

excel

bosinander

Iron Contributor

Oct 14, 2021

hannesvdhitcoza Hi. Depending on Excel version, and needs, there are different possibilities.

If XLOOKUP is not available;

B4 =VLOOKUP(B2;A10:M341;3;1)

vertically, lookup b2 in range a10:m341 and return that ranges column no 3.

Last parameter 1 means approximately match instead of exact; in the ranges actual order, lookup the first higher value, step back one 'and use that row.

Looking up in the helper column results in the next/higher value.

If XLOOKUP is available, use it!! The formula becomes easier;

B5 =XLOOKUP(B2;B10:B341;C10:C341;;1)

If the version also supports dynamic arrays (like Excel 365) it is possible to get the whole data row in return;

B6 =XLOOKUP(B2;B10:B341;B10:M341;;1)