How to lookup these values

[ajmal pottekattil yoousuf]

Based on the Weight and Class the Maximum permissible error is not pickup.

[#2]

@ajmal pottekattil yoousuf 

Enter the number 5000 in I5 instead of the text value 5000 kg.

You'll still see "5000 kg" because of the custom number format.

[#3]

In that worksheet both kg and gram are present

[#4 · Accepted solution]

@ajmal pottekattil yoousuf 

Currently, column A contains numbers with different custom number formats.

For example, A7, A20 and A32 all contain the number 500 (you can check this by selecting one of these cells and looking at the formula bar). Only the number format is different:

General" kg"

General" g"

General" mg"

The MATCH formula in cell Q2 only matches on cell value, not on cell formatting. If you enter the text 500 kg in cell J5, there is no match, since none of the cells in column A contains the text 500 kg.

So you will have to change all values in column A to the text values as displayed. For example, change A7 to 500 kg, change A20 to 500 g and change A32 to 500 mg.