Home » Case-Sensitive Revenue Lookup

Case-Sensitive Revenue Lookup

Provide the Excel formula to get Revenue in cell E3 for a company given in cell E2. This would be case-sensitive LOOKUP, hence for MSFT, the answer would be 64280 not 46222.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 9
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Case-Sensitive Revenue Lookup with Power Query

Power Query solution 1 for Case-Sensitive Revenue Lookup, proposed by Aditya Kumar Darak 🇮🇳: 
let
 Data   = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
 Criteria = Excel.CurrentWorkbook(){[ Name = "criteria" ]}[Content]{0}[Company],
 Final  = Data{[ Company = Criteria ]}[Revenue]
in
 Final
Will work only if have one match. If there are many and want the first result, then go with this one.
let
 Data   = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
 Criteria = Excel.CurrentWorkbook(){[ Name = "criteria" ]}[Content]{0}[Company],
 Filter  = Table.SelectRows ( Data, each [Company] = Criteria )[Revenue],
 Final  = List.First ( Filter )
in
 Final

Solving the challenge of Case-Sensitive Revenue Lookup with Excel

Excel solution 1 for Case-Sensitive Revenue Lookup, proposed by محمد حلمي: 
=SUM(EXACT(A2:A8,E2)*B2:B8)
Excel solution 2 for Case-Sensitive Revenue Lookup, proposed by محمد حلمي: 
=FILTER(
    B2:B8;
    SUBSTITUTE(
        A2:A8;
        E1;
        
    )=""
)

=SUM(
    FILTER(
        B2:B8;
        SUBSTITUTE(
        A2:A8;
        E1;
        
    )=""
    )
)
Excel solution 3 for Case-Sensitive Revenue Lookup, proposed by 🇰🇷 Taeyong Shin: 
=XLOOKUP(E2, A2:A8, B2:B8,, 3)
Excel solution 4 for Case-Sensitive Revenue Lookup, proposed by Julian Poeltl: 
=FILTER(B2:B8,EXACT(A2:A8,E2))
Excel solution 5 for Case-Sensitive Revenue Lookup, proposed by Timothée BLIOT: 
=FILTER(A2:B8,EXACT(A2:A8,E2))
Excel solution 6 for Case-Sensitive Revenue Lookup, proposed by Bhavya Gupta: 
=INDEX(B2:B8,MATCH(TRUE,EXACT(A2:A8,E2),0))
Excel solution 7 for Case-Sensitive Revenue Lookup, proposed by Charles Roldan: 
=LOOKUP(TRUE,EXACT(E2,A2:A8),B2:B8)
Excel solution 8 for Case-Sensitive Revenue Lookup, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
={INDEX(
    $B$2:$B$8,
    MATCH(
        TRUE,
        EXACT(
            E2,
            $A$2:$A$8
        ),
        0
    )
)
Excel solution 9 for Case-Sensitive Revenue Lookup, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=INDEX(B:B;SMALL(IF(ISNUMBER(FIND(UPPER(E2);LEFT(A2:A8;LEN(A2:A8));1));ROW(A2:A8);"");1))
Excel solution 10 for Case-Sensitive Revenue Lookup, proposed by CA Raghunath Gundi: 
=SUM(--IF(EXACT(E161,B161:B167),C161:C167))
Excel solution 11 for Case-Sensitive Revenue Lookup, proposed by CA Raghunath Gundi: 
=INDEX(B:B,SMALL(IF(ISNUMBER(FIND(E2,A2:A8,1)),ROW(B2:B8),""),1))
Excel solution 12 for Case-Sensitive Revenue Lookup, proposed by Excel BI: 
=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(E2,A2:A8),B2:B8),2,FALSE)
Excel solution 13 for Case-Sensitive Revenue Lookup, proposed by Jardiel Euflázio: 
=SUM(IFERROR(--SUBSTITUTE(A2:A8,E2,B2:B8),0))
Excel solution 14 for Case-Sensitive Revenue Lookup, proposed by Jardiel Euflázio: 
=FILTER(B2:B8,EXACT(E2,A2:A8))

=SUM(EXACT(E2,A2:A8)*B2:B8)

=0+CONCAT(IF(EXACT(E2,A2:A8),B2:B8,""))

=INDEX(B2:B8,MATCH(1,0+EXACT(E2,A2:A8),0))
Excel solution 15 for Case-Sensitive Revenue Lookup, proposed by Victor Momoh (MVP, MOS, R.Eng): 
=FILTER(B2:B8,EXACT(A2:A8,E2))
Excel solution 16 for Case-Sensitive Revenue Lookup, proposed by Cary Ballard, DML: 
=SUM(FILTER(B2:B8, ISNUMBER(FIND(E2, A2:A8))))
Excel solution 17 for Case-Sensitive Revenue Lookup, proposed by Viswanathan M B: 
=Average(if(Exact(A2:A8, E2),B2:B8,””))
Excel solution 18 for Case-Sensitive Revenue Lookup, proposed by Juliano Santos Lima: 
=FILTER(B2:B8,EXACT(E2,A2:A8))
Excel solution 19 for Case-Sensitive Revenue Lookup, proposed by Ibrahim Sadiq: 
=LOOKUP(1,1/(EXACT(A2:A8,E2)),B2:B8)
Excel solution 20 for Case-Sensitive Revenue Lookup, proposed by Muthukumar R.: 
VLOOKUP(TRUE,
     CHOOSE ({1,
    2},
    EXACT(
        E1,
        A1:A2
    ),
    B1:B2),
    2,
    FALSE)
Excel solution 21 for Case-Sensitive Revenue Lookup, proposed by Mehdi HAMMADI: 
=SUMPRODUCT(EXACT(
    A2:A8;
    E2
)*(B2:B8))
Excel solution 22 for Case-Sensitive Revenue Lookup, proposed by Lotfi GARZOUN, MCT 🪄 Excel – Power BI Trainer 🇹🇳 🇸🇩: 
=XLOOKUP(
    TRUE;
    EXACT(
        E2;
        A2:A8
    );
    B2:B8;
    "Not Found"
)
=SUMPRODUCT((EXACT(
    A2:A8;
    E2
)*(B2:B8)))
=INDEX(
    B2:B8;
    MATCH(
        TRUE;
        EXACT(
    A2:A8;
    E2
);
        0
    )
)
=INDEX(
    B:B;
    SMALL(
        IF(
            ISNUMBER(
                FIND(
                    UPPER(
                        E2
                    );
                    LEFT(
                        A2:A8;
                        LEN(
                            A2:A8
                        )
                    );
                    1
                )
            );
            ROW(
                            A2:A8
                        );
            ""
        );
        1
    )
)
=SUM(
    FILTER(
        B2:B8;
        ISNUMBER(
            FIND(
        E2;
        A2:A8
    )
        );
         ""
    )
)
=FILTER(
    B2:B8;
    EXACT(
        E2;
        A2:A8
    )
)
=SUM(
    EXACT(
        E2;
        A2:A8
    )*B2:B8
)
=0+CONCAT(
    IF(
        EXACT(
        E2;
        A2:A8
    );
        B2:B8;
        ""
    )
)
=INDEX(
    B2:B8;
    MATCH(
        1;
        0+EXACT(
        E2;
        A2:A8
    )
    );
    0
)
=AVERAGE(
    IF(
        EXACT(
            A2:A8;
             E2
        );
        B2:B8;
        ""
    )
)
=VLOOKUP(
    TRUE;
     CHOOSE(
         {12};
          EXACT(
        E2;
        A2:A8
    );
          B2:B8
     );
     2;
     0
)
=XLOOKUP(
    TRUE;
     EXACT(
            A2:A8;
             E2
        );
     B2:B8;
     “NA”;
     0;
     -1
)
=FILTER(
    B2:B8;
    SUBSTITUTE(
        A2:A8;
        E2;
        
    )=""
)
=SUM(
    FILTER(
        B2:B8;
        SUBSTITUTE(
        A2:A8;
        E2;
        
    )=""
    )
)
=SUM(
    IFERROR(
        --SUBSTITUTE(
            A2:A8;
            E2;
            B2:B8
        );
        0
    )
)
=LOOKUP(1;
    1/(EXACT(
    A2:A8;
    E2
));
    B2:B8)
=OFFSET(
    A1;
    MATCH(
        TRUE;
        EXACT(
        E2;
        A2:A8
    );
        0
    );
    1
)
=IF(INDIRECT("A"&(1+MATCH(
    TRUE;
    EXACT(
            A2:A8;
             E2
        );
     0
)))<>"";
     =INDEX(
         B2:B8;
          MATCH(
              TRUE;
               EXACT(
            A2:A8;
             E2
        );
               0
          )
     );
     "")
=AGGREGATE(
    14;
    6;
    B2:B8/FIND(
        E2;
         A2:A8
    );
    1
)
Excel solution 23 for Case-Sensitive Revenue Lookup, proposed by Sharjeel A.: 
=SUMPRODUCT(EXACT(A2:A8),E2)*(B2:B8))
Excel solution 24 for Case-Sensitive Revenue Lookup, proposed by Vatsin Thaker: 
=OFFSET(A1,MATCH(TRUE,EXACT(E2,A2:A8),0),1)
Excel solution 25 for Case-Sensitive Revenue Lookup, proposed by Karol Janulewicz: 
=sum(filter(B:B, isnumber(find(E2, A:A)), ""))
Excel solution 26 for Case-Sensitive Revenue Lookup, proposed by Lotfi GARZOUN, MCT 🪄 Excel – Power BI Trainer 🇹🇳 🇸🇩: 
=SUMPRODUCT((EXACT(A2:A8,E2)*(B2:B8)))
Excel solution 27 for Case-Sensitive Revenue Lookup, proposed by David Gilbert Kamanyi: 
=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(I5,E4:F11),F4:F11),2,FALSE)
Excel solution 28 for Case-Sensitive Revenue Lookup, proposed by Lotfi GARZOUN, MCT 🪄 Excel – Power BI Trainer 🇹🇳 🇸🇩: 
=IF(INDIRECT("A"&(1+MATCH(
    TRUE;
    EXACT(
        A2:A8;
         E2
    );
     0
)))<>"";
     INDEX(
         B2:B8;
          MATCH(
              TRUE;
               EXACT(
        A2:A8;
         E2
    );
               0
          )
     );
     "")

Solving the challenge of Case-Sensitive Revenue Lookup with Python in Excel

Python in Excel solution 1 for Case-Sensitive Revenue Lookup, proposed by Alejandro Campos: 
df = xl("A1:B8", headers=True)
target_company = xl("E2")
matching_revenue = df.loc[df['Company'] == target_company, 'Revenue'].iloc[0]
target_company, matching_revenue
Python in Excel solution 2 for Case-Sensitive Revenue Lookup, proposed by Aditya Kumar Darak 🇮🇳: 
data = xl("A1:B8", True)
criteria = xl("E2")
result = data[data["Company"] == criteria]["Revenue"].iloc[-1]
result

Leave a Reply