Hi all,
How do I write a VLOOKUP would cover the entire table, the second VLOOKUP would begin one row below what the first one found?

Thanks in advance
Regards


EggHeadCafe - Software Developer Portal of Choice
ASP.NET Database Cache Dependency
http://www.eggheadcafe.com/tutorials...-cache-de.aspx

This is what i trying to do:

Column A Column B Column C Column D
1 Aston Villa Man Utd 2 5
2 Sunderland Aston Villa 0 1
3 Chelsea Liverpool 0 0
4 Aston Villa Wigan 0 2

Formula on F1=IF(VLOOKUP("aston villa",A1:D10,3,FALSE)1.5,"Over","Under")

Table Array for formula on F2 will start one row after "Aston Villa" is found, looking like =IF(VLOOKUP("aston villa",A1+1:D10,3,FALSE)1.5,"Over","Under") and the next vlookup will look into table array of A4+1:A10.

Thanks and Regards



Leong David wrote:

VLOOKUP would begin one row below what the first one found?
21-Nov-09

Hi all,
How do I write a VLOOKUP would cover the entire table, the second VLOOKUP would begin one row below what the first one found?

Thanks in advance
Regards

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
A Good Solution for "Magic String" Data
http://www.eggheadcafe.com/tutorials...-for-magi.aspx

Hi,


Try this ARRAY formula method. See below on how to enter and array and when
it is array entered dag down for the second and third occurrence etc.

=IF(INDEX($C$1:$C$13,LARGE(($A$1:$A$13="AV")*ROW($ A$1:$A$13),COUNTIF($A$1:$A$13,"AV")+1-ROW(A1)))1.5,"Over","Under")

Note for testing I shortened Aston Villa to AV so change back. In practice i
wouldn't use the name in the formula, id use a cell reference. In this case
E1 hold Aston Villa

=IF(INDEX($C$1:$C$13,LARGE(($A$1:$A$13=E1)*ROW($A$ 1:$A$13),COUNTIF($A$1:$A$13,E1)+1-ROW(A1)))1.5,"Over","Under")

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

P.S I don't like the nil nil for Chelski- Liverpool, I prefer 0 - 5. You'll
never walk alone.

Mike

"Leong David" wrote:

This is what i trying to do:

Column A Column B Column C Column D
1 Aston Villa Man Utd 2 5
2 Sunderland Aston Villa 0 1
3 Chelsea Liverpool 0 0
4 Aston Villa Wigan 0 2

Formula on F1=IF(VLOOKUP("aston villa",A1:D10,3,FALSE)1.5,"Over","Under")

Table Array for formula on F2 will start one row after "Aston Villa" is found, looking like =IF(VLOOKUP("aston villa",A1+1:D10,3,FALSE)1.5,"Over","Under") and the next vlookup will look into table array of A4+1:A10.

Thanks and Regards



Leong David wrote:

VLOOKUP would begin one row below what the first one found?
21-Nov-09

Hi all,
How do I write a VLOOKUP would cover the entire table, the second VLOOKUP would begin one row below what the first one found?

Thanks in advance
Regards

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
A Good Solution for "Magic String" Data
http://www.eggheadcafe.com/tutorials...-for-magi.aspx
.

Hi Mike,
Thanks a zillion for the suggestion. But it did not work, got #NUM! error for the first formula after changing AV to aston villa and "over" for the second formula which should output "under" because AV=0.

Thanks and Regards



Mike H wrote:

Hi,Try this ARRAY formula method.
21-Nov-09

Hi,


Try this ARRAY formula method. See below on how to enter and array and when
it is array entered dag down for the second and third occurrence etc.

=IF(INDEX($C$1:$C$13,LARGE(($A$1:$A$13="AV")*ROW($ A$1:$A$13),COUNTIF($A$1:$A$13,"AV")+1-ROW(A1)))1.5,"Over","Under")

Note for testing I shortened Aston Villa to AV so change back. In practice i
would not use the name in the formula, id use a cell reference. In this case
E1 hold Aston Villa

=IF(INDEX($C$1:$C$13,LARGE(($A$1:$A$13=E1)*ROW($A$ 1:$A$13),COUNTIF($A$1:$A$13,E1)+1-ROW(A1)))1.5,"Over","Under")

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You cannot type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

P.S I do not like the nil nil for Chelski- Liverpool, I prefer 0 - 5. You'll
never walk alone.

Mike

"Leong David" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
DPAPI Encrypt and store sensitive data
http://www.eggheadcafe.com/tutorials...d-store-s.aspx

Hi Mike,
Thanks a zillion, it worked...i did not press crtl, shift and enter ....thanks

Regards



Leong David wrote:

VLOOKUP would begin one row below what the first one found?
21-Nov-09

Hi Mike,
Thanks a zillion for the suggestion. But it did not work, got #NUM! error for the first formula after changing AV to aston villa and "over" for the second formula which should output "under" because AV=0.

Thanks and Regards

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
SQL Server 2000 How-To Videos for Beginner DBAs
http://www.eggheadcafe.com/tutorials...-howto-vi.aspx