CODEWITHSUNDEEP
excel-formula
chaim
chaim

Reputation: 3

get results of index and match and return the first match into the first column second match in second column and so on

I'm setting up a points system on excel.

I have a database of kids with a barcode assigned to each of them. I have a database of points with a barcode assigned to each of them.

I have a sheet where the kids can scan their private barcode and then the barcode of the points they got and the points are going onto their account.

I'm stuck on the part of adding the points they Scand to their account.

I tried to use this following ArrayFormula but the sheet got to slow

=ArrayFormula(iferror(index('kids Scanning Page'!$H:$H,small(if($A2='kids Scanning Page'!$C:$C,row('kids Scanning Page'!$C:$C),""),sum(1)))))

I tried to use this index and match formula

=INDEX('kids Scanning Page'!$I:$I,MATCH($A$2,'kids Scanning Page'!$D:$D,0))

but this is only giving me the first match

can someone please suggest me what I can do to solve my problem?

the following is to understand my formulas

kids Scanning Page'!$H:$H = the points the kid got for all transaction 
$a2 = the database of the kid's barcode 
kids Scanning Page'!$C:$C = unique id for each barcode transaction 
kids Scanning Page'!$I:$I = the points the kid got for this transaction 
kids Scanning Page'!$D:$D = the column were the kids can scan their barcodes

Upvotes: 0

Views: 52

Answers (1)

JvdV
JvdV

Reputation: 75840

Well first thing I'm thinking is a helper column, see this link

If a helper is no option, then at least don't use array formulas with whole columns, that does slow down things signicicantly. Instead use absolute ranges e.g. $A$2:$A$100 for example. Or, if the range must be dynamic use a non-volatile option to get to the last used row instead of the whole column.

So instead of 

MATCH(criteria,A:A....

You can use for example:

MATCH(criteria,$A$1:INDEX(A:A,COUNTA(A:A))....

Or:

MATCH(criteria,$A$1:INDEX($A:$A,MATCH("ZZZ",$A:$A))....

To create a smaller range to calculate through array formula.

Upvotes: 1

Related Questions