match
 

Hi,

Is there anyone can help me to solve the problem?? I need the answer from
the following table by match colum and row, there is some things worng with
the formula,any ideas??
=INDEX(E6:AT47,IF(MATCH(AG37,B6:B47,0),(MATCH(AG39 ,D6:D47,0))),IF(MATCH(AO37,E3:AT3,0),(MATCH(AO39,E 5:AT5,0))))


A A B B C C C D D D D E E
E E F F
100 200 100 200 100 200 300 100 200 300 400
100 200 300 400 100 200

F 100 1 1 2 3 5 6 8 9 10
11 3 4 6 7 9 12 4
F 200 1 3 7 3 3 6 5 6 3 7 8 1
6 3 8 4
E 100 2 4 2 7 7 8 6 8 7 2 9 2 8
6 9
E 200 3 3 3 2 2 9 8 9 2 3 10 3 9 7
E 300 5 5 4 3 3 10 9 10 3 4 11 5 3
E 400 6 6 3 4 4 11 10 11 4 3 8 8
D 100 8 8 5 3 3 3 11 3 3 12 9
D 200 9 9 6 5 5 4 3 4 9 7
D 300 10 10 8 6 6 6 4 6 3
D 400 11 11 9 8 8 7 6 8
C 100 3 3 10 9 9 9 11
C 200 4 4 11 10 10 5
C 300 6 6 3 11 3
B 100 7 7 4 3
B 200 9 9 6
A 100 12 12
A 200 4

match
 

Maybe...

=INDEX(E6:AT47,
IF(isnumber(MATCH(AG37,B6:B47,0)),MATCH(AG37,B6:B4 7,0),MATCH(AG39,D6:D47,0)),
IF(isnumber(MATCH(AO37,E3:AT3,0)),MATCH(AO37,E3:AT 3,0),MATCH(AO39,E5:AT5,0)))



pt wrote:

Hi,

Is there anyone can help me to solve the problem?? I need the answer from
the following table by match colum and row, there is some things worng with
the formula,any ideas??
=INDEX(E6:AT47,IF(MATCH(AG37,B6:B47,0),(MATCH(AG39 ,D6:D47,0))),IF(MATCH(AO37,E3:AT3,0),(MATCH(AO39,E 5:AT5,0))))

A A B B C C C D D D D E E
E E F F
100 200 100 200 100 200 300 100 200 300 400
100 200 300 400 100 200

F 100 1 1 2 3 5 6 8 9 10
11 3 4 6 7 9 12 4
F 200 1 3 7 3 3 6 5 6 3 7 8 1
6 3 8 4
E 100 2 4 2 7 7 8 6 8 7 2 9 2 8
6 9
E 200 3 3 3 2 2 9 8 9 2 3 10 3 9 7
E 300 5 5 4 3 3 10 9 10 3 4 11 5 3
E 400 6 6 3 4 4 11 10 11 4 3 8 8
D 100 8 8 5 3 3 3 11 3 3 12 9
D 200 9 9 6 5 5 4 3 4 9 7
D 300 10 10 8 6 6 6 4 6 3
D 400 11 11 9 8 8 7 6 8
C 100 3 3 10 9 9 9 11
C 200 4 4 11 10 10 5
C 300 6 6 3 11 3
B 100 7 7 4 3
B 200 9 9 6
A 100 12 12
A 200 4



--

Dave Peterson

match
 

Your IF statements are incorrect, as their is no logical test performed. It
appears as if you want to either use the MATCH of AG37 or AG39, but what
determines the choice? For instance, if you want the greater of AG37 or AG39,
perhaps change that part to:
IF(AG37AG39,MATCH(AG37,B6:B47,0),MATCH(AG39,D6:D4 7,0))

Again, both of your IF statements lack a logical test.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"pt" wrote:

Hi,

Is there anyone can help me to solve the problem?? I need the answer from
the following table by match colum and row, there is some things worng with
the formula,any ideas??
=INDEX(E6:AT47,IF(MATCH(AG37,B6:B47,0),(MATCH(AG39 ,D6:D47,0))),IF(MATCH(AO37,E3:AT3,0),(MATCH(AO39,E 5:AT5,0))))


A A B B C C C D D D D E E
E E F F
100 200 100 200 100 200 300 100 200 300 400
100 200 300 400 100 200

F 100 1 1 2 3 5 6 8 9 10
11 3 4 6 7 9 12 4
F 200 1 3 7 3 3 6 5 6 3 7 8 1
6 3 8 4
E 100 2 4 2 7 7 8 6 8 7 2 9 2 8
6 9
E 200 3 3 3 2 2 9 8 9 2 3 10 3 9 7
E 300 5 5 4 3 3 10 9 10 3 4 11 5 3
E 400 6 6 3 4 4 11 10 11 4 3 8 8
D 100 8 8 5 3 3 3 11 3 3 12 9
D 200 9 9 6 5 5 4 3 4 9 7
D 300 10 10 8 6 6 6 4 6 3
D 400 11 11 9 8 8 7 6 8
C 100 3 3 10 9 9 9 11
C 200 4 4 11 10 10 5
C 300 6 6 3 11 3
B 100 7 7 4 3
B 200 9 9 6
A 100 12 12
A 200 4

match
 

thank you for what I am look for is:-

when input AG37=A and AG39=100 ( colum)
input AO37=F and AO39=100 (row)
by matching the above input the answer should be =1
how to match first match on "A" then match the "100" within the "A" colum or
row ???

"Luke M" wrote:

Your IF statements are incorrect, as their is no logical test performed. It
appears as if you want to either use the MATCH of AG37 or AG39, but what
determines the choice? For instance, if you want the greater of AG37 or AG39,
perhaps change that part to:
IF(AG37AG39,MATCH(AG37,B6:B47,0),MATCH(AG39,D6:D4 7,0))

Again, both of your IF statements lack a logical test.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"pt" wrote:

Hi,

Is there anyone can help me to solve the problem?? I need the answer from
the following table by match colum and row, there is some things worng with
the formula,any ideas??
=INDEX(E6:AT47,IF(MATCH(AG37,B6:B47,0),(MATCH(AG39 ,D6:D47,0))),IF(MATCH(AO37,E3:AT3,0),(MATCH(AO39,E 5:AT5,0))))


A A B B C C C D D D D E E
E E F F
100 200 100 200 100 200 300 100 200 300 400
100 200 300 400 100 200

F 100 1 1 2 3 5 6 8 9 10
11 3 4 6 7 9 12 4
F 200 1 3 7 3 3 6 5 6 3 7 8 1
6 3 8 4
E 100 2 4 2 7 7 8 6 8 7 2 9 2 8
6 9
E 200 3 3 3 2 2 9 8 9 2 3 10 3 9 7
E 300 5 5 4 3 3 10 9 10 3 4 11 5 3
E 400 6 6 3 4 4 11 10 11 4 3 8 8
D 100 8 8 5 3 3 3 11 3 3 12 9
D 200 9 9 6 5 5 4 3 4 9 7
D 300 10 10 8 6 6 6 4 6 3
D 400 11 11 9 8 8 7 6 8
C 100 3 3 10 9 9 9 11
C 200 4 4 11 10 10 5
C 300 6 6 3 11 3
B 100 7 7 4 3
B 200 9 9 6
A 100 12 12
A 200 4