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

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

Hi,Try this ARRAY formula method.

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

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

It Worked : )

Hi Mike,

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



Regards