I would like to use INDEX MATCH in a BYROW function to populate an entire column with the output of the INDEX MATCH.
There are 2 criteria for the INDEX MATCH function.
Criteria 1 is in row 2 of the current column (indirect(ADDRESS(2,column(),4)
), and is looked up in named rang SUG_Drawing.
Criteria 2 is in column 1 of he current row (indirect(ADDRESS(row(),1,4)
), and is lookup in named range SUG_EXC_PN.
The function below outputs data from named range SUG_QTY when both criteria are met. This function works as shown in Column J
index(SUG_QTY,match(1,index((indirect(ADDRESS(2,column(),4))=SUG_Drawing) * (indirect(ADDRESS(ROW(),1,4))=SUG_EXC_PN),0,1),0))
I want to use the BYROW function to iterate through the rows of criteria 2. So I ended up with the formula below:
=BYROW(INDIRECT(ADDRESS(3,COLUMN()+1,4)&”:”&ADDRESS(B1,D1,4)), LAMBDA(q,INDEX(SUG_QTY,MATCH(1,INDEX(INDIRECT(ADDRESS(2,column(),4))=SUG_Drawing) * (INDIRECT(ADDRESS(q,1,4))=SUG_EXC_PN),0))))
The issue is the output for every row is the following error “Did not find value ‘1’ in MATCH evaluation.”. As shown in column K If I change the name, q, to a number I know works, the output for every row is the same. As shown in column L.
Desired result is for 1 function to be put in row k3, that then outputs the same data as in J3:J243.