Need help with INDEX/MATCH formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bexi - 06 Mar 2008 18:59 GMT

Hello,

I have two worksheets where the results are formula driven. The data
for the worksheets come from two different sources.

The result of worksheet A is the following:
FIJOL/AGNIESZKA    91009    50.00
FIJOL/AGNIESZKA    91009    1,718.80
    91009    1,518.51
    91009 555.92
    91009 -
    73001    150.00
    73001    19.95

The result of Worksheet B is the following:
93002    JORDAN/VAUGHN    333.53    F08GXCB218
93002    JORDAN/VAUGHN    0    F08GXCB218
93002    JORDAN/VAUGHN    0    F08GXCB218
93002    JORDAN/VAUGHN    0    F08GXCB218
91009    FIJOL/AGNIESZKA    1718.8    F08GXSA308

The INDEX/MATCH formula works for one MATCH formula,
=INDEX('Sheet B'!$D$2:$D$100, MATCH('Sheet A'!A2,'Sheet B'!$B$2:$B
$100,0)), the result is F08GXSA308.

But, it does not work when I tried two match formulas
=INDEX('Sheet B'!$D$2:$D$100, MATCH('Sheet A'!A2,'Sheet B'!$B$2:$B
$100,0), MATCH('Sheet A'!B2,'Sheet B'!$A$2:$A$100,0))

The format of the sheet A and B is General.
The condition is to match card #, name, amount.

Please help.
Thank you
Bexi

Reply to this Message

Bob Phillips - 06 Mar 2008 19:17 GMT

I think that you want

=INDEX('Sheet B'!$D$2:$D$100, MATCH(1,
('Sheet A'!A2='Sheet B'!$B$2:$B)*
('Sheet A'!B2='Sheet B'!$A$2:$A$100),0))

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello,
>
[quoted text clipped - 31 lines]
> Thank you
> Bexi

Reply to this Message

Dave Peterson - 06 Mar 2008 19:34 GMT

Just a typo ($100 was missing from the $B portion):

=INDEX('Sheet B'!$D$2:$D$100, MATCH(1,
('Sheet A'!A2='Sheet B'!$B$2:$B$100)*
('Sheet A'!B2='Sheet B'!$A$2:$A$100),0))

> I think that you want
>
[quoted text clipped - 45 lines]
> > Thank you
> > Bexi

Signature

Dave Peterson

Reply to this Message

Bexi - 07 Mar 2008 14:44 GMT

> I think that you want
>
[quoted text clipped - 47 lines]
>
> - Show quoted text -

It work, but it has be entered as array formula (ctrl-shift-enter). I
tried this formula earlier but I forgot about the ctrl-shift-enter.

Thank you.
Bexi

Reply to this Message

Rate this thread: