Forum Discussion

Copper Contributor

Jan 15, 2022

Solved

Request a help

quantity of books to be ordered specs 1000 2000 3000 5000 Internal papers type paper 170 grams 4 colors $22.00 $20.50 $19.00 $17.00...

excel

PeterBartholomew1

Jan 15, 2022

Elaref_S_BEN_OTMAN

For 2-way lookups reverting to INDEX/MATCH is often simplest.

The alternative formula takes advantage of Excel 365 to create a more verbose formula

= LET(
    qtyLevel,    XMATCH(qtyOrdered,volume,1),
    paperOption, XMATCH(paperOrdered,paperType),
    coverOption, XMATCH(coverOrdered,coverType),
    cost, INDEX(paperCost, paperOption, qtyLevel)
        + INDEX(coverCost, coverOption, qtyLevel),
    IFERROR(cost, "")
  )

Doug_Robbins_Word_MVP

MVP

Jan 15, 2022

Your question appears to be incomplete. Can you complete it.

Elaref_S_BEN_OTMAN

Copper Contributor

Jan 15, 2022

Thank you for your hint Robbins Doug_Robbins_Word_MVP

The question is how can make a function or a scenario so I let the user that help him choose the number of pages, type of cover, and the quantity of the printed books as those are the major factors to evaluate the price of the printed books, note that the data are applicable to expand to several tens of items.

Doug_Robbins_Word_MVP
MVP
Jan 15, 2022
Elaref_S_BEN_OTMAN

The way I would go about it would be to create a userform containing either radio buttons or comboboxes for each of the variables:

Number of pages

Paper type

Cover type

and then, depending upon the selections made, compute the cost based on those selections