Hlookup + Vlookup

It is not clear from your pictures if the values are pure numbers to which the units have been added by custom formatting, or if you have typed the units in along with the values (thus making them text values). It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Hope this helps.

Pete

Thank you Shukla, please find attached the excel file (simplified with different cells)

You will need to change the entries in B5 and B6 so that they are pure numbers - use the format painter icon to apply the same custom format from G8 to B5 and from G6 to B6, then enter 120 in B5 and 10.8 in B6 (though you would enter it as 10,8), and then you can use this formula in B7:

=IFERROR(HLOOKUP(INDEX(G6:T6,MATCH(B5,G8:T8,0))+B6,G6:T8,3,0),"")

Hope this helps.

Pete

This is probably because some decimal fractional values cannot be represented with complete accuracy in the binary system which computers use. Fractional values in binary are representations of repeated division by 2, so although 0.5 can be represented exactly, it turns out that 0.1 and other simple decimal fractions are recurring values in binary, and thus have some inherent error built in (in the same way that 0.666 and 0.667 in decimal are NOT completely accurate representations of the fraction 2/3.

Thus, when two fractions are added together, this small error in each can build up so that an exact match is not found when using 0 (or FALSE) as the last parameter in a MATCH or HLOOKUP function. By changing the formula to this:

=IFERROR(HLOOKUP(INDEX(G6:T6,MATCH(B5,G8:T8))+B6,G6:T8,3),"")

(as the TRUE parameter is assumed, if omitted), then it should work more consistently.

Hope this helps.

Pete

Thank you Pete.. works like a charm

hi pete, I have question about your post when Cell value are pure numbers, with formatting applied to then does excel perform calculation using pure numbers or the formatted value?

You shouldn't really post a question in someone else's thread (read Forum Rule 02) - instead, you should start your own thread, referring back to this one if necessary.

However, just to answer your query, formatting does not change the underlying value of a cell, so if you apply a Custom Format to a cell and then do some arithmetic on it, it will be the value in the cell that is used in the calculation.

Hope this helps.

Pete