Interpretation for: =INDEX(MATCH(1 <--one

[Guest]

Interpretation for:
=INDEX(F1:F100,MATCH(1,(A1:A100="Custname")*(D1100="State"),0)) Enter with
Ctrl+Shift+Enter

Dear All,

I came across this formula and was wondering how to interpret:

Match(1,(xxxxxx)*(xxxxxxx) = why 1 and not 2 or 3 or so forth (i tried 2 and
3 but doesn't work)


I tested the formula in my workbook

=INDEX(Dbase!B1:B22,MATCH(1,(Dbase!C1:C22=B1)*(Dbase!A1:A22=B2),0)) => works

=INDEX(Dbase!B1:B22,MATCH(2,(Dbase!C1:C22=B1)*(Dbase!A1:A22=B2),0)) =>
doesn't work


Thanks.

 

[Guest]

Match(1,(xxxxxx)*(xxxxxxx),0) = why 1 and not 2 or 3 ...

It's because the lookup array in the MATCH, this part:
(A1:A100="Custname")*(D1100="State")
will resolve to an array of ones/zeros, eg: {0;0;1;0;0;0;0;...}
depending on where the dual criteria is satisfied (1's) or not (0's)

Using the lookup value: 1 in MATCH would hence give us the (1st) matching
position within the array where the dual criteria is satisfied. It's presumed
of course, that there should be only a single matching position (ie a
single/unique instance of 1) to be returned within the array. That position
(a number) returned is then used by the INDEX part of the expression (eg:
INDEX(F1:F100, ...) to yield the required result.

Hope the above clarifies it.

---

 

[Guest]

Thanks Max.

I get it, the 1 and 0. Looking back at the function box, the match criteria
is 1. That's how it was matched.

Appreciate it!!

 

[Max]

welcome, glad it clarifies.