# Need help with INDEX/MATCH formula

B

#### 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.

Thank you
Bexi

B

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

D

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

B

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