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

andri witana

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

Kelly Moore

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

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

andri witana

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

@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

=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

=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), ""))

andri witana

@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