VLOOKUP would begin one row below what the first one found?
 

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

VLOOKUP would begin one row below what the first one found?
 

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

VLOOKUP would begin one row below what the first one found?
 

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
.

VLOOKUP would begin one row below what the first one found?
 

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

It Worked : )
 

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