Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to match 2 columns, if a match found add info from 2nd column | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions |