How to fill the Pandas Dataframe based on values from another two dataframes

If you can rely on the fact, that your ranges in Num_df don't overlap, you can do this as follows. Note I define some helper functions, you could also do without, but I think it makes it a bit easier to read.

# convert the datatypes (guess your real data does not store numeric values in strings)
Num_df[['Inclusive', 'Exclusive']]= Num_df[['Inclusive', 'Exclusive']].astype('float32')
Actual_df[['Quantity', 'Rating']]=Actual_df[['Quantity', 'Rating']].astype('float32')

# define two helper functions (or just store the categories / variables in different dataframes)
def get_variable_data(df, variable):
    df= df.loc[df['Variable'] == variable, ['Cats', 'Value']].copy()
    df.set_index(['Cats'], inplace=True)
    df.columns= [variable + '_value']
    return df

def get_num_data(df, variable):
    df= df.loc[df['Variable'] == variable, ['Inclusive', 'Value']].copy()
    df.sort_values(['Inclusive'], inplace=True)
    df.columns=[variable + '_inclusive', variable + '_value']

# join the first part by a regular join
Joined_df= Actual_df
for cat in ['Color', 'Fruit']:
    Joined_df= Joined_df.merge(get_variable_data(Cat_df, cat), left_on=[cat], right_index=True, how='left')

# now join according ranges using asof
for cat in ['Quantity', 'Rating']:
    print(cat)
    Joined_df= pd.merge_asof(Joined_df, get_num_data(Num_df, cat), left_on=[cat], right_on='Inclusive', direction='backward', suffixes=['', '_'+cat])

# drop the excess columns
Joined_df.drop([col for col in Joined_df if col.endswith('_inclusive')], axis='columns', inplace=True)

# the result of this is
    Color   Fruit  Quantity  Rating  Color_value  Fruit_value  Quantity_value  Rating_value
0  yellow  banana       4.0     0.5      -0.2836       0.3648          0.2145       -0.4378

As written above, the last step with the merge_asof asumes that your ranges contain no gaps, where you don't have a value and span the whole value range. Because of this, you wouldn't need to check the end of the range. However if that assumption is not correct, you just have to change the code a bit:

1. use merge_asof as it is, just alter get_num_data, so it also returns the Exclusive column.

2. use Join_df.loc[Joined_df[cat]>=Joined_df[cat + '_exclusive'], cat]=defaultvalue to delete the values that exceed the exclusive range.

Btw, it is really safe to do it this way, because if there is a row, in which the value of the cat column lies, then it will be selected by merge_asof because it searches for the biggest available Inclusive value, that is smaller or equal than the value in col (I mean, at least if you have no overlapping ranges, but it seems that is unlikely for a constellation as in your example).