Seeking Improvement on excel function

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

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

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

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

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

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

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

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

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

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