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

[Leong David]

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/tutorial...b5-1bf5e36f9c6d/aspnet-database-cache-de.aspx

 

[Leong David]

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",A110,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+110,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/tutorial...f3-aa98ed6669c3/a-good-solution-for-magi.aspx

 

[Mike H]

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]

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 whe
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
would not use the name in the formula, id use a cell reference. In this cas
E1 hold Aston Vill

=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+Ente
'and not just Enter. If you do it correctly then Excel will put curly bracket
'around the formula {}. You cannot type these yourself. If you edit the formul
'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'l
never walk alone

Mik

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
DPAPI Encrypt and store sensitive data
http://www.eggheadcafe.com/tutorial...c-d876c238a315/dpapi-encrypt-and-store-s.aspx

 

[Leong David]

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/tutorial...ae-b4e56fb5b1c6/sql-server-2000-howto-vi.aspx