IFERROR, INDEX.AND, and MATCH function in one formula

Options
andri witana
andri witana ✭✭
edited 01/22/21 in Formulas and Functions

hi all

plz check my formula

=IFERROR(INDEX({Daftar Sales Order in Progress Range 1}; AND(MATCH(Blok@row; {Daftar Sales Order in Progress Range 5}; 1); MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 1))); "No Data")


basically i want to return a value of saya column A, if column B and column C in source sheet and current sheet is matching.


but somehow all is #NO MATCH, in this case i use IFERROR so it turn out "No Data"


UPDATE

i change the formula to

=INDEX({Daftar Sales Order in Progress Range 0}; AND(MATCH(Blok@row; {Daftar Sales Order in Progress Range 5}); MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4})))

the result is #INVALID REF

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hi Andri

    Index Match only works with one set of criteria. When you have multiple criteria that need to met as part of the 'match', we use INDEX COLLECT. By default, every term listed within a COLLECT function is an 'AND' condition.

    =IFERROR(INDEX(COLLECT({Daftar Sales Order in Progress Range 1};{Daftar Sales Order in Progress Range 5},@cell=Blok@row, {Daftar Sales Order in Progress Range 4},@cell=[Nomor Kapling]@row);1);"No Data")

    Kelly

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @andri witana ,

    Try:

    =IFERROR(INDEX({Daftar Sales Order in Progress Range 1}; IF(MATCH(Blok@row; {Daftar Sales Order in Progress Range 5}; 0)=MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), "")), "No Data")

    I didn't test it so you might need to do some tweaking.

    Any help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • andri witana
    Options

    hi @KDM

    this formula

    =IFERROR(INDEX(COLLECT({Daftar Sales Order in Progress Range 1};{Daftar Sales Order in Progress Range 5},@cell=Blok@row, {Daftar Sales Order in Progress Range 4},@cell=[Nomor Kapling]@row);1);"No Data")

    the result is = #unparseable

    to better see where the problem is, i dont use IFERROR now

    =INDEX(COLLECT({Daftar Sales Order in Progress Range 1}; {Daftar Sales Order in Progress Range 5}; =Blok@row; {Daftar Sales Order in Progress Range 4}; =[Nomor Kapling]@row); {Daftar Sales Order in Progress Range 1})

    this formula result is #invalid data type

    hi @Mark Cronk

    =(INDEX({Daftar Sales Order in Progress Range 1}; IF(MATCH(Blok@row; {Daftar Sales Order in Progress Range 5}; 0)=MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), ""))

    the result is #unparseable

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    @andri witana

    I think some terms were removed that were part of the INDEX/COLLECT when you removed the IFERROR.

    Try this please

    =INDEX(COLLECT({Daftar Sales Order in Progress Range 1};{Daftar Sales Order in Progress Range 5};@cell=Blok@row; {Daftar Sales Order in Progress Range 4};@cell=[Nomor Kapling]@row);1)

    Also, when you created these ranges - is each range it's own single column? Please note you can rename the ranges so instead of them saying 'Range 4' for example, you can reference the column they refer to.

  • andri witana
    Options

    =INDEX(COLLECT({Daftar Sales Order in Progress Range 1};{Daftar Sales Order in Progress Range 5};@cell=Blok@row; {Daftar Sales Order in Progress Range 4};@cell=[Nomor Kapling]@row);1)

    this formula result is #invalid value

    the ranges is single column, i clicked the blue colored reference another sheet.

    thx before @KDM

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    =INDEX({Daftar Sales Order in Progress Range 1}; IF(MATCH(Blok@row; {Daftar Sales Order in Progress Range 5}; 0)=MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), MATCH([Nomor Kapling]@row; {Daftar Sales Order in Progress Range 4}; 0), ""))


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • andri witana
    Options

    @KDM and @Mark Cronk thx for your assistance

    i understand how to use index, collect and match better

    for my problem i decide to create a new column that combine key identifier column

    then i applied this formula

    =IFERROR(INDEX({Link SO}; (MATCH([Kode Kapling]@row; {Daftar SO}))); "")

    works, and less formula to check on.

    thx a lot u both

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!