Need help with INDEX/MATCH formula

[Bexi]

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

 

[Bob Phillips]

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))


--
---
HTH

Bob


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

 

[Dave Peterson]

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))

 

[Bexi]

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))

--
---
HTH

Bob

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













- 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