Match the Upper closest value using Index Match.

Hi Everyone,

I couldn't resolve the formula of Index-Match for getting the values that matches the following criteria

1) Match the "Category" of Table 1 to Table 2

2) Match the "value" of Table 1 to Table 2, if value is not present in Table 2 take the upper closest value.

Once the above conditions are met, Take the Result from Table 2

Table 1 is for Input and Table 2 is for Database.

Please note that Data in Table 2 should not be sorted in any way. I tried to use the -1 option of the Match function for upper closest value but it didnt work out.

Have a look at the file attached. Any help will be greatly appreciated.
IndexMatchUpperClosestValue_AlongwithExactMatch.xlsx

What a great community.

Thank you so much benishiryo.

Originally Posted by benishiryo

hi adnanaddo, welcome to the forum. try this in C3 copied down:

Please Login or Register  to view this content.

Although problem is solved but I have to sort the Value from Largest to Smallest then sort the Category to make it work.

I don't want the user to sort the data every time he enters the value in the database. Hope there is a solution without VBA code.