Lookup value from row and return higher if not available

[JimmyA]

Hi All

Hopefully I can explain this but if an example sheet is need please let me know.

I have a vlaue in cell A1, lets say "H"
I have a value in Cell A2, lets say 11000

I now have a range of A4:J15. Column A contains letters from A-L, the rest of the range contains numbers.

What i am trying to get is a formula that will take the value from cell A1 to find what row that is on and if the value in cell A2 is in that row then just return it otherwise if the value is not found then return round up to an available value in that row.

I have written this formula but it seems to round down rather than up.

=HLOOKUP(A2,$B$4:$J$15,MATCH(A1,$A$4:$A$15,0))

any help would be great.

Thanks in advance.

Jim

[dilipandey]

Hi Jim,

and if the value in cell A2 is in that row then just return it otherwise if the value is not found then return round up to an available value in that row.

If value found than return that, if not found than return value which is immediate greater than that, see the attached file - green cells. thanks.

lookup value in a row and return higher.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[JimmyA]

Hi DILIPandey

Thanks very much for this. It is exactly what I need.

Thanks again.

Jim

[dilipandey]

You are welcome Jim...

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>