Forum Discussion

ShainaDay88's avatar
ShainaDay88
Copper Contributor
Jun 30, 2023

XLOOKUP multiple lookup values and return checkbox if all return values <> a specified value!

I have to be overthinking this, but I can't keep "spinning my wheels" - Any advice would be very much appreciated!

I have two worksheets. I have attached an example with parts of the worksheets in two separate tables. I am trying to come up with a formula that will look up each of the "Trade In" values (columns G - L) from Green Table 1 (Used) in Blue Table 2 (Trades) and if all of the trades have a status of "Sold", I want a checkbox returned in "All Trades Sold" (column N) - I feel certain this can be done, but I am so frustrated trying to figure it out! I've attached an example workbook with the mentioned tables and a screenshot below for reference. 

 

 

excel
Formulas and Functions
office 365

2 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 01, 2023

    Hi ShainaDay88 

     

    Assuming the workbook you shared reflects reality your challenge started with Trades stored as Numbers in columns G-L, stored as Text in column Q. Matching Numbers in Text doesn't work with lookup functions. Converted Trades in Q as Numbers in attached file

     

     

    One way, in N3:

    =LET(
  trades,    COUNT(G3:L3),
  lookvalue, FILTER(G3:L3, ISNUMBER(G3:L3)),
  aresold,   XLOOKUP(lookvalue, TRADES[TRADE SERIAL NO.], TRADES[TRADE STATUS],  "") = "sold",
  IFS(trades = 0,"", SUM(--aresold) = trades,"þ", TRUE,"")
)

     

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Jun 30, 2023

    This kind of output?

     select * from Sheet2 limit 3;

    select * from Sheet1 limit 3;

    cli_one_dim~Sheet1~5;

    select `Serial No.`,count(*),sum(f02='Sold'),group_concat(f01||f02),iif(count(*)=sum(f02='Sold'),'√','') `All Trades Sold` from Sheet1union left join Sheet2 on 数量=f01 group by `Serial No.`;

Resources