Seeking Improvement on excel function

[wilchong via OfficeKB.com]

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

 

[T. Valko]

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)

 

[wilchong via OfficeKB.com]

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

 

[T. Valko]

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.

 

[wilchong via OfficeKB.com]

Dear T.Valko,
The formular working very well.

Many thanks,
Wilson

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Dear T.Valko,
The formular working very well.

Many thanks,
Wilson

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

 

[wilchong via OfficeKB.com]

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

You're welcome. Thanks for the feedback!

Dear T.Valko,
The formular working very well.

[quoted text clipped - 27 lines]

 

[T. Valko]

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

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

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

 

[wilchong via OfficeKB.com]

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

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]

 

[T. Valko]

I'm not following you on this. Post some sample data and explain what result
you expect.

--
Biff
Microsoft Excel MVP

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

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

 

[wilchong via OfficeKB.com]

Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would like
to break the whole thing into stages and explain step by step.

Just before I talk about the complicate function, first of all, I would like
to ask you one minor question regarding your previous function. Your
previous suggested function as follow:
=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0),$E1),LOOKUP($E1,E$21:F$30),
"-")
I placed this formula in the cell of G1 and drag it from G1 to G10. Because
the formula detects E2, so “G†will show in the cell of G2. “G†is come the
database as follow:

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

What I want to do the first thing is to revise the above formula slightly.
The above formula will achieve the result based on the variable from E1 to
E10, NOT from F1 to F10 as show you just now. So my first question is that
do you think the argument of above function can be changed to F1 rather than
E1?

Many thanks for your time,
Wilchong

I'm not following you on this. Post some sample data and explain what result
you expect.

Dear T. Valko,
Thanks, your suggested function work very well, it is exactly fit to what

[quoted text clipped - 49 lines]

 

[T. Valko]

The original formula in F1:F10 returns either a dash "-" or "OK".

Are you saying that you want another formula that checks F1:F10 for "OK" and
if found do a lookup on the entries in E1:E10?

If that's what yo want try this:

=IF(F1="OK",LOOKUP(E1,E$21:F$30),"-")

--
Biff
Microsoft Excel MVP

Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would
like
to break the whole thing into stages and explain step by step.

Just before I talk about the complicate function, first of all, I would
like
to ask you one minor question regarding your previous function. Your
previous suggested function as follow:
=IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0),$E1),LOOKUP($E1,E$21:F$30),
"-")
I placed this formula in the cell of G1 and drag it from G1 to G10.
Because
the formula detects E2, so "G" will show in the cell of G2. "G" is come
the
database as follow:

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

What I want to do the first thing is to revise the above formula slightly.
The above formula will achieve the result based on the variable from E1 to
E10, NOT from F1 to F10 as show you just now. So my first question is
that
do you think the argument of above function can be changed to F1 rather
than
E1?

Many thanks for your time,
Wilchong

I'm not following you on this. Post some sample data and explain what
result
you expect.

Dear T. Valko,
Thanks, your suggested function work very well, it is exactly fit to
what

[quoted text clipped - 49 lines]

Thanks for your advice,
Wilchong

 

[wilchong via OfficeKB.com]

Dear T. Valko,
Thanks, your suggested function work very well. With your suggested formula,
the “G†shown up in the of G2 when the formula detect “OK†in F2. OK, the
first step is ok already.

The second step is how to revise the formula in order to make the “G†shown
up in the of G1 when the formula detect “OK†from F1 to F10. From my
previous experience you show me, I can add “=IF(ROWS(G1:G$10)<=SUM(--(COUNTIF
(……………..†and then entered by "Shift + Control + Enter". I also dragged the
function from G1 to G10. Of course, what I tried to do was failed. As a
result, I need your advice.

Many thanks for your time.
Wilchong

The original formula in F1:F10 returns either a dash "-" or "OK".

Are you saying that you want another formula that checks F1:F10 for "OK" and
if found do a lookup on the entries in E1:E10?

If that's what yo want try this:

=IF(F1="OK",LOOKUP(E1,E$21:F$30),"-")

Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would

[quoted text clipped - 45 lines]

 

[T. Valko]

OK, I think I uderstand what you want.

I would put all the table data in a contiguous range like this:

...........E..........F..........G
1.......11.........T..........-
2.......12.........G........OK
3.......13.........R..........-
4.......14.........E..........-

Then:

Formula in H1:

=COUNTIF(G1:G10,"OK")

Then this array formula** in H2 and copied down:

=IF(ROWS(H$2:H2)>H$1,"",INDEX(F$1:F$10,SMALL(IF(G$1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

Dear T. Valko,
Thanks, your suggested function work very well. With your suggested
formula,
the "G" shown up in the of G2 when the formula detect "OK" in F2. OK, the
first step is ok already.

The second step is how to revise the formula in order to make the "G"
shown
up in the of G1 when the formula detect "OK" from F1 to F10. From my
previous experience you show me, I can add
"=IF(ROWS(G1:G$10)<=SUM(--(COUNTIF
(......." and then entered by "Shift + Control + Enter". I also dragged
the
function from G1 to G10. Of course, what I tried to do was failed. As a
result, I need your advice.

Many thanks for your time.
Wilchong

The original formula in F1:F10 returns either a dash "-" or "OK".

Are you saying that you want another formula that checks F1:F10 for "OK"
and
if found do a lookup on the entries in E1:E10?

If that's what yo want try this:

=IF(F1="OK",LOOKUP(E1,E$21:F$30),"-")

Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would

[quoted text clipped - 45 lines]

Thanks for your advice,
Wilchong

 

[wilchong via OfficeKB.com]

Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter. So,
I tried to modify your suggested formula. After revised your formula, it
should have two functions as follow:
1) Detect any “OK†in column F;
2) Once able to identify “OK†which refer to “12â€, the array formula will
base on the below database show “O†in the cell G1.

The desire result is show below (of course, I failed to combine both
functions in your array formula).
RESULT:-
..........E..........F..........G
1.......11......... - ......... O
2.......12........OK........ -
3.......13......... - ......... -
4.......14......... - ......... -

The revised formula is placed in G1 and drag it to G4. Please note that the
revised formula is not necessary place in G1 and correspondent to column F,
that is why I want to put an array formula.

DATABASE:-
“O†refers to “11â€; “E†refers to “12â€; etc.
….............E............F
21..……...11....……O
22……….12….……E
23……….13………O
24……….14………E
25……….15………O
26……….16………E
27……….17………O
28……….18………E
29……….19………O
30……....20…....…E

Once again, many thanks for your advice,
Wilchong

OK, I think I uderstand what you want.

I would put all the table data in a contiguous range like this:

..........E..........F..........G
1.......11.........T..........-
2.......12.........G........OK
3.......13.........R..........-
4.......14.........E..........-

Then:

Formula in H1:

=COUNTIF(G1:G10,"OK")

Then this array formula** in H2 and copied down:

=IF(ROWS(H$2:H2)>H$1,"",INDEX(F$1:F$10,SMALL(IF(G$1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Dear T. Valko,
Thanks, your suggested function work very well. With your suggested

[quoted text clipped - 30 lines]

 

[T. Valko]

Why would the result in G1 be O when 11 is not found in the table?

This is getting harder and harder to follow!

--
Biff
Microsoft Excel MVP

Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter.
So,
I tried to modify your suggested formula. After revised your formula, it
should have two functions as follow:
1) Detect any "OK" in column F;
2) Once able to identify "OK" which refer to "12", the array formula
will
base on the below database show "O" in the cell G1.

The desire result is show below (of course, I failed to combine both
functions in your array formula).
RESULT:-
.........E..........F..........G
1.......11......... - ......... O
2.......12........OK........ -
3.......13......... - ......... -
4.......14......... - ......... -

The revised formula is placed in G1 and drag it to G4. Please note that
the
revised formula is not necessary place in G1 and correspondent to column
F,
that is why I want to put an array formula.

DATABASE:-
"O" refers to "11"; "E" refers to "12"; etc.
..............E............F
21.......11......O
22....12....E
23....13...O
24....14...E
25....15...O
26....16...E
27....17...O
28....18...E
29....19...O
30......20......E

Once again, many thanks for your advice,
Wilchong

OK, I think I uderstand what you want.

I would put all the table data in a contiguous range like this:

..........E..........F..........G
1.......11.........T..........-
2.......12.........G........OK
3.......13.........R..........-
4.......14.........E..........-

Then:

Formula in H1:

=COUNTIF(G1:G10,"OK")

Then this array formula** in H2 and copied down:

=IF(ROWS(H$2:H2)>H$1,"",INDEX(F$1:F$10,SMALL(IF(G$1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Dear T. Valko,
Thanks, your suggested function work very well. With your suggested

[quoted text clipped - 30 lines]

Thanks for your advice,
Wilchong

 

[wilchong via OfficeKB.com]

Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed, the
formula which I wish to have is quite hard and challenging.

The reason why I really need to place the array formula in G1 is because
considering only a few “OK†(five out of 200 data) will be shown in column F.
Furthermore, I will put this array formula in other spreadsheet at the end of
the day. Finally, I want to save space as well.

Actually, before asking your advice, I was trying very hard in fitting
different database functions in array formula, of course, it failed. As a
result, under desperate situation, I have to seeking the advice from the
excel “guru†like you. Really appreciate your advice and time.

Once again, many thanks for your advice,
Wilchong

Why would the result in G1 be O when 11 is not found in the table?

This is getting harder and harder to follow!

Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter.

[quoted text clipped - 68 lines]

 

[T. Valko]

I don't see how O relates to 12 (OK)?

I thought you wanted the results that correspond to "OK"?

At this point I'm lost!

--
Biff
Microsoft Excel MVP

Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed,
the
formula which I wish to have is quite hard and challenging.

The reason why I really need to place the array formula in G1 is because
considering only a few "OK" (five out of 200 data) will be shown in
column F.
Furthermore, I will put this array formula in other spreadsheet at the end
of
the day. Finally, I want to save space as well.

Actually, before asking your advice, I was trying very hard in fitting
different database functions in array formula, of course, it failed. As
a
result, under desperate situation, I have to seeking the advice from the
excel "guru" like you. Really appreciate your advice and time.

Once again, many thanks for your advice,
Wilchong

Why would the result in G1 be O when 11 is not found in the table?

This is getting harder and harder to follow!

Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter.

[quoted text clipped - 68 lines]

Thanks for your advice,
Wilchong

 

[wilchong via OfficeKB.com]

Really sorry Valko, may be thinking too much formulas has made me "hang over"
!!

Yes, you are right. Should be "E", which relate to 12 (OK)! "O" is for 11,
13, 15 and etc!

Many thanks,
Wilson

I don't see how O relates to 12 (OK)?

I thought you wanted the results that correspond to "OK"?

At this point I'm lost!

Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed,

[quoted text clipped - 26 lines]

 

[wilchong via OfficeKB.com]

Dear T. Valko,
Really sorry to make thing so complicated!, sorry about that! After
considering your suggestion, I have changed the database in order to make the
thing simple.

One thing need your help, below is the database, I need a formula to extract
all the data from A1 to A10.
…...........A
1..……...TY
2………. -
3……….ER
4……….SX
5………. -
6………. -
7……….SX
8……….TY
9………. -
10……....ER

The formula I wish to extract the data from A1 to A10 will show the result as
follow: to list the data from B1 to B6:
…...........B
1..…….. TY
2……… ER
3……… SX
4……… SX
5……… TY
6………. ER
7………. -
8………. -
9………. -
10……... -


And again, from the previous experience you have shown me, I can use “=IF
(ROWS(B$1:B10)<=SUM(--(COUNTIF(…………… †and then entered by "Shift + Control
+ Enter". I also dragged the formula from B1 to B10. Of course, what I tried
to do was failed. As a result, I need your advice.

Many thanks,
Wilchong

I don't see how O relates to 12 (OK)?

I thought you wanted the results that correspond to "OK"?

At this point I'm lost!

Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed,

[quoted text clipped - 26 lines]