![]() |
If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
![]()
Dear sir,
I have one question regarding an "index" formular together with "match". I used to set the following excel function to pick the data from the database: =INDEX("database",MATCH("column argument","column range",0),"target column range"). One of key thing of this excel funation is "target column range", which only allow to cover ONE column only. My question: Is there any way to improve or change the above excel function in order to make "target column range" can cover more column ranges. I have also thought about Vlookup and lookup function, but failed. Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#2
|
|||
|
|||
![]()
Not enough detail.
See if this helps... ...........A..........B..........C .....................Red......Blue 1........x.........10........12 2........y.........14........18 3........z.........22........30 If you have descriptive column headers like the sample table then you can use a MATCH function to define the column. To lookup "y" and "Blue"... E1 = y F1 = blue =INDEX(B2:C4,MATCH(E1,A2:A4,0),MATCH(F1,B1:C1,0)) Or: =VLOOKUP(E1,A1:C4,MATCH(F1,A1:C1,0),0) -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9ac23842c2204@uwe... Dear sir, I have one question regarding an "index" formular together with "match". I used to set the following excel function to pick the data from the database: =INDEX("database",MATCH("column argument","column range",0),"target column range"). One of key thing of this excel funation is "target column range", which only allow to cover ONE column only. My question: Is there any way to improve or change the above excel function in order to make "target column range" can cover more column ranges. I have also thought about Vlookup and lookup function, but failed. Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#3
|
|||
|
|||
![]()
Dear T. Valko,
Thanks for your guidance. Based on your data on the table, I wish I can instruct excel function to show “OK” if it detects 12 within row 1 (the argument is “x”) in the cell E2 as below table. I will place the excel function (with two variables: 12 in the cell E2 and “x”) from F1 to F10. E1 = 11 F1 = - E2 = 12 F2 = “OK” E3 = 13 F3 = - E4 = 14 F4 = - E5 = 15 F5 = - E6 = 16 F6 = - E7 = 17 F7 = “OK” E8 = 18 F8 = - E9 = 19 F9 = - E10 = 20 F10 = - Many thanks for your advice, Wilchong wilchong wrote: Dear sir, I have one question regarding an "index" formular together with "match". I used to set the following excel function to pick the data from the database: =INDEX("database",MATCH("column argument","column range",0),"target column range"). One of key thing of this excel funation is "target column range", which only allow to cover ONE column only. My question: Is there any way to improve or change the above excel function in order to make "target column range" can cover more column ranges. I have also thought about Vlookup and lookup function, but failed. Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
#4
|
|||
|
|||
![]()
Try something like this...
D1 = x =IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), E1),"OK","-") Copy down as needed. -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9ac35ae0d0fe3@uwe... Dear T. Valko, Thanks for your guidance. Based on your data on the table, I wish I can instruct excel function to show "OK" if it detects 12 within row 1 (the argument is "x") in the cell E2 as below table. I will place the excel function (with two variables: 12 in the cell E2 and "x") from F1 to F10. E1 = 11 F1 = - E2 = 12 F2 = "OK" E3 = 13 F3 = - E4 = 14 F4 = - E5 = 15 F5 = - E6 = 16 F6 = - E7 = 17 F7 = "OK" E8 = 18 F8 = - E9 = 19 F9 = - E10 = 20 F10 = - Many thanks for your advice, Wilchong wilchong wrote: Dear sir, I have one question regarding an "index" formular together with "match". I used to set the following excel function to pick the data from the database: =INDEX("database",MATCH("column argument","column range",0),"target column range"). One of key thing of this excel funation is "target column range", which only allow to cover ONE column only. My question: Is there any way to improve or change the above excel function in order to make "target column range" can cover more column ranges. I have also thought about Vlookup and lookup function, but failed. Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
![]()
Dear T.Valko,
The formular working very well. Many thanks, Wilson wilchong wrote: Dear T. Valko, Thanks for your guidance. Based on your data on the table, I wish I can instruct excel function to show “OK” if it detects 12 within row 1 (the argument is “x”) in the cell E2 as below table. I will place the excel function (with two variables: 12 in the cell E2 and “x”) from F1 to F10. E1 = 11 F1 = - E2 = 12 F2 = “OK” E3 = 13 F3 = - E4 = 14 F4 = - E5 = 15 F5 = - E6 = 16 F6 = - E7 = 17 F7 = “OK” E8 = 18 F8 = - E9 = 19 F9 = - E10 = 20 F10 = - Many thanks for your advice, Wilchong Dear sir, I have one question regarding an "index" formular together with "match". I [quoted text clipped - 11 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#6
|
|||
|
|||
![]()
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9ac3ebe080250@uwe... Dear T.Valko, The formular working very well. Many thanks, Wilson wilchong wrote: Dear T. Valko, Thanks for your guidance. Based on your data on the table, I wish I can instruct excel function to show "OK" if it detects 12 within row 1 (the argument is "x") in the cell E2 as below table. I will place the excel function (with two variables: 12 in the cell E2 and "x") from F1 to F10. E1 = 11 F1 = - E2 = 12 F2 = "OK" E3 = 13 F3 = - E4 = 14 F4 = - E5 = 15 F5 = - E6 = 16 F6 = - E7 = 17 F7 = "OK" E8 = 18 F8 = - E9 = 19 F9 = - E10 = 20 F10 = - Many thanks for your advice, Wilchong Dear sir, I have one question regarding an "index" formular together with "match". I [quoted text clipped - 11 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#7
|
|||
|
|||
![]()
Dear T. Valko,
Based on the source database, your suggested function =IF(COUNTIF(INDEX(B$2:C $4,MATCH(D$1,A$2:A$4,0),0),E1),"OK","-") will show “OK” if it detects 12 within row 1 (the argument is “x”) in the cell E2. Every things work very perfect. In order to make the analysis more intensive, I would like, based on the data (F1 to F10), I need a function to analyse the data based on another database, see below. E21 = 11……………… F21 = T E22 = 12……………… F22 = G E23 = 13……………… F23 = R E24 = 14……………… F24 = E E25 = 15……………… F25 = K E26 = 16……………… F26 = Q E27 = 17……………… F27 = L E28 = 18……………… F28 = C E29 = 19……………… F29 = Z E30 = 20……………… F30 = I I tried to use function MODE plus IF to construct a formula, based on the database above, to show “G” if the formula detect “OK” along “12”, but failed. Can you advice me other option to do this! Many thanks, Wilchong T. Valko wrote: You're welcome. Thanks for the feedback! Dear T.Valko, The formular working very well. [quoted text clipped - 27 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#8
|
|||
|
|||
![]()
If I understand what you want then maybe this:
D1 = x =IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), E1),LOOKUP(E1,E$21:F$30),"-") -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9ac5211fe38b0@uwe... Dear T. Valko, Based on the source database, your suggested function =IF(COUNTIF(INDEX(B$2:C $4,MATCH(D$1,A$2:A$4,0),0),E1),"OK","-") will show "OK" if it detects 12 within row 1 (the argument is "x") in the cell E2. Every things work very perfect. In order to make the analysis more intensive, I would like, based on the data (F1 to F10), I need a function to analyse the data based on another database, see below. E21 = 11...... F21 = T E22 = 12...... F22 = G E23 = 13...... F23 = R E24 = 14...... F24 = E E25 = 15...... F25 = K E26 = 16...... F26 = Q E27 = 17...... F27 = L E28 = 18...... F28 = C E29 = 19...... F29 = Z E30 = 20...... F30 = I I tried to use function MODE plus IF to construct a formula, based on the database above, to show "G" if the formula detect "OK" along "12", but failed. Can you advice me other option to do this! Many thanks, Wilchong T. Valko wrote: You're welcome. Thanks for the feedback! Dear T.Valko, The formular working very well. [quoted text clipped - 27 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#9
|
|||
|
|||
![]()
Dear T. Valko,
Thanks, your suggested function work very well, it is exactly fit to what I am think! I have found out that your suggested function, IF(COUNTIF(INDEX(B$2:C$4,MATCH (D$1,A$2:A$4,0),0),$E1),LOOKUP($E1,E$21:F$30),"-"), depends on two arguments, one is the D1, another cell is E1. Right now, I want to do something little bit complicated. I want to make your suggested function with additional function, which is only show the data from F21 to F30 from lowest value to greatest value (Based on the value from the cell, E21 to E30). This function has to be done without the argument from the cell, E1. In order to achieve the objective, I tried to add "IF(ROWS(H21:H$21)=SUM(-- (COUNTIF(A$2:A$4,$E$21:$F$30)=0)) in front of your suggested function. I also add " ROWS(H$21:H21))),"") " at the back of your suggested formula. The new function is located in the cell, H21, then entered by "Shift + Control + Enter". I also dragged the function from H21 to H23. If D1 = y, my desire outcome is to see "E" show in the cell H21, "C" show in the cell H22 and "-" show in the cell H23. Of course, up to this point, the new function doesn't work! I am really doubt my revised function can be improved based on my requirement, I need your advice. Many thanks for your time. Wilchong T. Valko wrote: If I understand what you want then maybe this: D1 = x =IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0) ,E1),LOOKUP(E1,E$21:F$30),"-") Dear T. Valko, Based on the source database, your suggested function [quoted text clipped - 35 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
#10
|
|||
|
|||
![]()
I'm not following you on this. Post some sample data and explain what result
you expect. -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9adc3708900ee@uwe... Dear T. Valko, Thanks, your suggested function work very well, it is exactly fit to what I am think! I have found out that your suggested function, IF(COUNTIF(INDEX(B$2:C$4,MATCH (D$1,A$2:A$4,0),0),$E1),LOOKUP($E1,E$21:F$30),"-"), depends on two arguments, one is the D1, another cell is E1. Right now, I want to do something little bit complicated. I want to make your suggested function with additional function, which is only show the data from F21 to F30 from lowest value to greatest value (Based on the value from the cell, E21 to E30). This function has to be done without the argument from the cell, E1. In order to achieve the objective, I tried to add "IF(ROWS(H21:H$21)=SUM(-- (COUNTIF(A$2:A$4,$E$21:$F$30)=0)) in front of your suggested function. I also add " ROWS(H$21:H21))),"") " at the back of your suggested formula. The new function is located in the cell, H21, then entered by "Shift + Control + Enter". I also dragged the function from H21 to H23. If D1 = y, my desire outcome is to see "E" show in the cell H21, "C" show in the cell H22 and "-" show in the cell H23. Of course, up to this point, the new function doesn't work! I am really doubt my revised function can be improved based on my requirement, I need your advice. Many thanks for your time. Wilchong T. Valko wrote: If I understand what you want then maybe this: D1 = x =IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0 ),E1),LOOKUP(E1,E$21:F$30),"-") Dear T. Valko, Based on the source database, your suggested function [quoted text clipped - 35 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
Thread Tools | |
Display Modes | |
|
|