Loop through excels and extract values in excel using pandas

I can't really follow your code (you should try using more expressive variable names, not only for others sake but also for you if you later want to understand or change parts of the code), but I assume that the problem is mainly because of the indexes.

The way you construct this results in INDDEDIN and FAMDEDIN each consisting of two rows which both have the index 0, so using concat will cause problems. You could try something like pd.concat([INDDEDIN, FAMDEDIN], axis=1, ignore_index=True) if you are sure the order in them always is perfectly aligned anyway. A better way to go about this would be to clean up the way you create the DataFrame.

The way I see it you also assign values to INDDEDIN and FAMDEDIN within the loop and then use them for the first time outside the loop, resulting in only the last iterations value being used for the part after the loop.

Although there are many ways one way I like to create a DataFrame in an iteration is to create a nested dictionary and then using pandas.DataFrame.from_dict().

Edit: Here is a way you could do it (and make it more readable):

result = {}

from pathlib import Path

p = Path(path to excel files)
# filter files
filtered_files = [x for x in p.glob("**/*.xlsx") if not x.name.__contains__("R4")]
# iterate over filtered files
for file in filtered_files:
    # read file into a DataFrame
    full_df = pd.read_excel(file, sheet_name=[1], header=1)
    # get column 1 name
    col_1_name = full_df.columns[1]
    # get partial dataframe with rows filtered by value in column 1
    ded_ind_df = full_df[full_df[col_1_name]=='Deductible Individual'] 
    # get partial dataframe with rows filtered by value in column 1
    ded_fam_df = full_df[full_df[col_1_name]=='Deductible Family']
    # add results for this file to the results dictionary
    # EDIT: added [0] to just get element instead of an element in a list
    result[file] = {
        'INDIVIDUAL DEDUCTIBLE INN': list(ded_ind_df['In-Network\nVALUE'])[0], 
        'FAMILY DEDUCTIBLE INN': list(ded_fam_df['In-Network\nVALUE'])[0]
    }

# convert results dictionary to DataFrame
# EDIT: added .transpose() to swap between rows and columns
result = pd.DataFrame.from_dict(result).transpose()

This will result in a DataFrame where the indices are the file names, if you instead want indexes you can wrap filtered_files using for i, file in enumerate(filtered_files) and then use result[i] = instead of result[file] =.