Forum Discussion

gab2020's avatar
gab2020
Copper Contributor
Jul 08, 2020
Solved

Comparing a cell value against a table

Hi, I'm looking for 2 formulas to compare a value against a table in excel to return 1) a match and 2) another value. Below is an example of the excel table. What should be the input formulas in th...
excel
Formulas and Functions
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Jul 08, 2020

    gab2020 

    If the layout of the data is similar to as per the screenshot below...

     

    You may try these formulas to get the desired outputs...

     

    In B8

     

    =IF(COUNTIF(C2:H5,A8),"True","False")

     

     

    In B9

     

    =INDEX(B2:B5,AGGREGATE(15,6,1/(C2:H5=A9)*(ROW(C2:H5)-ROW(C2)+1),1))

     

     

    Please refer to the attached for more details.

     

Resources