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

Options
edited 01/22/21

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

• ✭✭✭✭✭✭
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

• ✭✭✭✭✭✭
Options

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.

• 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

=(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

• ✭✭✭✭✭✭
Options

I think some terms were removed that were part of the INDEX/COLLECT when you removed the 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)

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.

• 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

• ✭✭✭✭✭✭
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.

• 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!