Vlookup / choose

[How_Do_I]

A little lost here…why isn’t the formula in C9 working please?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Norwich City	Watford	0	2	06-Aug-10	2	3		Reading	Scunthorpe United	1	1	07-Aug-10	1	2
2	Bristol City	Millwall	0	1	07-Aug-10	0	3		Burnley	Nottingham Forest	1	0	07-Aug-10	1	0
3	Coventry City	Portsmouth	1	0	07-Aug-10	2	0		Crystal Palace	Leicester City	3	0	07-Aug-10	3	2
4	Hull City	Swansea City	1	0	07-Aug-10	2	0		Middlesbrough	Ipswich Town	1	0	07-Aug-10	1	3
5	Preston North End	Doncaster Rovers	0	2	07-Aug-10	0	2		Leeds Utd	Derby County	1	2	07-Aug-10	1	2
6	QPR	Barnsley	1	0	07-Aug-10	4	0		Cardiff City	Sheffield United	0	1	08-Aug-10	1	1
7
8	Lookup Value
9	Coventry City	Portsmouth
Sheet1

#VALUE!

 

[Marcelo Branco]

Hi,

The VLOOKUPs in C9 returns column C or K that contains numbers.

So you need

=LOOKUP(9.99E+307,CHOOSE({1,2,3},"",VLOOKUP($A9,$I$1:$O$6,3,0),VLOOKUP($A9,$A$1:$G$6,3,0)))

M.

 

[How_Do_I]

Thanks Marcelo...I had a feeling it was something to do with that...

So if I didn't know if the return was a number or text I'd have to use something like the following formulas for B10 and C10 I assume...

Excel Workbook
	A	B	C
8	Lookup Value	2	3
9	Crystal Palace	Leicester City	3
10		Leicester City	3
Sheet1

#VALUE!

 

[Marcelo Branco]

As an alternative

Array-formula in B9

=IF(COUNTIF($A$1:$J$6,$A$9)=0,"Not Found",INDIRECT(TEXT(MAX(IF($A$1:$J$6=A9,10000*ROW($A$1:$J$6)+COLUMN($A$1:$J$6)+1)),"\R0000\C0000"),0))

Ctrl+Shift+Enter

Drag to C9

M.

 

[aitkensam]

Maybe I misunderstood your req'ts but do you need to get so technical here. Wouldn't the below serve for both numbers or text?
=IFERROR(VLOOKUP($A9,$A$1:$G$6,3,0),VLOOKUP($A9,$I$1:$O$6,3,0))

 

[AdamL]

IFERROR

Perhaps not for Excel 2003, I guess.

 

[aitkensam]

Ah apologies. Well it's still basically the same idea.

=IF(ISERROR(VLOOKUP(A9,A1:G6,3,0)),VLOOKUP(A9,I1:O6,3,0),VLOOKUP(A9,A1:G6,3,0))

 

[How_Do_I]

Thanks everyone...