Index Match Array New Formula not an array ?

[fasullo]

Is there anyway to recreate this formula w/o it being an array ?

{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}

 

[bschwartz]

Can you explain what your goal is with this formula?

It appears to be doing this:

Look up the first partial match for cell C3 in the whole of column A, and return the value in column B for the same row.

What is an example of this?

 

[iliace]

Does this work?

=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000, SUMPRODUCT(ROW(INDIRECT("1:2000")), 1-ISERROR(FIND(C3, 'Master List'!$A$1:$A$2000)))),"ADD TO MASTER"))

 

[bschwartz]

Does this work?

=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000, SUMPRODUCT(ROW(INDIRECT("1:2000")), 1-ISERROR(FIND(C3, 'Master List'!$A$1:$A$2000)))),"ADD TO MASTER"))

I don't know if I'm doing something wrong, but I get a different result from this formula, as from the Op's. With this Indirect, I am always getting the value in B1.

 

[iliace]

You're right - it's returning the first row when there is no match... not much you can do about that, it's normal INDEX behavior.

You can use this longer version; it's uglier, but still not an array.

=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000, IF(0=SUMPRODUCT(ROW(INDIRECT("1:2000")), 1-ISERROR(FIND(C3, 'Master List'!$A$1:$A$2000))),NA(),SUMPRODUCT(ROW(INDIRECT("1:2000")), 1-ISERROR(FIND(C3, 'Master List'!$A$1:$A$2000))))),"ADD TO MASTER"))



Why don't you want an array?

 

[fasullo]

thanks guys. seems like an array is the easiest formula. not a fan of arrays not as user friendly and can take more computing power