Apr 19 2021 07:38 AM
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 formula
240 000,00 | 7 600,00 |
245 000,00 | 7 600,00 |
250 000,00 | 7 600,00 |
250 000,01 | 8 400,00 |
260 000,00 | 8 400,00 |
270 000,00 | 8 400,00 |
280 000,00 | 8 400,00 |
290 000,00 | 8 400,00 |
Apr 19 2021 11:42 AM
Oct 13 2021 07:48 AM
Oct 13 2021 08:23 AM
Oct 13 2021 11:02 PM - edited Oct 13 2021 11:03 PM
@mathetes I was looking for the same scenario and that is how I got to this discussion. It would be nice to see the answer to this discussion for future reference.
@Hans Vogelaarmaybe you can help?
Oct 14 2021 12:55 AM
@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)
Oct 14 2021 12:57 AM
@hannesvdhitcoza NB - some values are changed to visualize matching the correct row. Make sure to use your correct data.