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