what is the wrong of formula

[hktom]

dear sir

What is the wrong of the formula which should match sheet 2 column a and column d for sheet 1 a2,b2. Pls find attached file

=INDEX(Sheet2!$A$1:$D$150,MATCH(A2,Sheet1!$A$2:$A$2,0),MATCH(B2,Sheet1!$B$2:$B$2,0))

tks
tom

[Special-K]

MATCH(A2,Sheet1!$A$2:$A$2,0)
is matching A2 against itself, result 1

MATCH(B2,Sheet1!$B$2:$B$2,0)
is matching B2 against itself, result 1

The cell in INDEX(Sheet2!A1,D150,1,1) contains 42184 hence the value in Sheet1!D2

[Special-K]

You have to use INDEX(MATCH(IF...)))

Try

In D2
=INDEX(Sheet2!$A$1:$D$150,MATCH($A2,IF($B2=Sheet2!$D$2:$D$14,Sheet2!$A$1:$A$14),0),COLUMN()-3)
Array formula, use Ctrl-Shift-Enter

[hktom]

dear special k

sorry i still try but fail

=INDEX(Sheet2!$A$1:$D$200,MATCH(B2,Sheet2!$D$1:$D$200,0),MATCH(A2,Sheet2!$A$1:$A$200,0))

please kindly help

tom

[hktom]

Hi Special K
D2,e2 work but f2,g2 dont work

In D2
=INDEX(Sheet2!$A$1:$D$150,MATCH($A2,IF($B2=Sheet2!$D$2:$D$14,Sheet2!$A$1:$A$14),0),COLUMN()-3)

pls help

tks
tom

[hktom]

hi speical k

f2,g2,e3,f3,g3 dont work. pls help

tks a lot
tom

[bebo021999]

Try:

Please Login or Register  to view this content.

Ctrl-shift-enter

[hktom]

dear sir,

it dont work also when i put it in cell D2. please help

Tks
Tom