I am trying to simplify this nested IF Statement. It's similar to a LOOKUP
FUNCTION but the data and result is all in a single row. I was unable to get
any LOOKUP statement to work. VLOOKUP OR HLOOKUP.

=IF(A1=2009,B1,IF(C1=2009,D1,IF(E1=2009,F1,IF(G1=2 009,H1,0))))

A B C D E F G H I J
2001 1 2002 3 2009 10 2011 11 2012 12

The answer would be Column F = 10. For "2012" the answer would be 12.

Thanks.

With the data in A2:J2 and the year you want in A4, try:

=INDEX(B2:J2,MATCH(A4,A2:I2,FALSE))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"finster26" wrote in message
...
I am trying to simplify this nested IF Statement. It's similar to a LOOKUP
FUNCTION but the data and result is all in a single row. I was unable to
get
any LOOKUP statement to work. VLOOKUP OR HLOOKUP.

=IF(A1=2009,B1,IF(C1=2009,D1,IF(E1=2009,F1,IF(G1=2 009,H1,0))))

A B C D E F G H I
J
2001 1 2002 3 2009 10 2011 11 2012 12

The answer would be Column F = 10. For "2012" the answer would be 12.

Thanks.

One way for row 3
=INDEX(3:3,0,MATCH(2009,3:3,0)+1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"finster26" wrote in message
...
I am trying to simplify this nested IF Statement. It's similar to a LOOKUP
FUNCTION but the data and result is all in a single row. I was unable to
get
any LOOKUP statement to work. VLOOKUP OR HLOOKUP.

=IF(A1=2009,B1,IF(C1=2009,D1,IF(E1=2009,F1,IF(G1=2 009,H1,0))))

A B C D E F G H I
J
2001 1 2002 3 2009 10 2011 11 2012 12

The answer would be Column F = 10. For "2012" the answer would be 12.

Thanks.

"2009" at times would correctly not be found. The result is "#n/a". In this
case, How do I then show "0".

Thanks.

"Don Guillett" wrote:

One way for row 3
=INDEX(3:3,0,MATCH(2009,3:3,0)+1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"finster26" wrote in message
...
I am trying to simplify this nested IF Statement. It's similar to a LOOKUP
FUNCTION but the data and result is all in a single row. I was unable to
get
any LOOKUP statement to work. VLOOKUP OR HLOOKUP.

=IF(A1=2009,B1,IF(C1=2009,D1,IF(E1=2009,F1,IF(G1=2 009,H1,0))))

A B C D E F G H I
J
2001 1 2002 3 2009 10 2011 11 2012 12

The answer would be Column F = 10. For "2012" the answer would be 12.

Thanks.

=IF(ISNA(MATCH(2009,4:4,0)),0,the formula presented)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"finster26" wrote in message
...
"2009" at times would correctly not be found. The result is "#n/a". In
this
case, How do I then show "0".

Thanks.

"Don Guillett" wrote:

One way for row 3
=INDEX(3:3,0,MATCH(2009,3:3,0)+1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"finster26" wrote in message
...
I am trying to simplify this nested IF Statement. It's similar to a
LOOKUP
FUNCTION but the data and result is all in a single row. I was unable
to
get
any LOOKUP statement to work. VLOOKUP OR HLOOKUP.

=IF(A1=2009,B1,IF(C1=2009,D1,IF(E1=2009,F1,IF(G1=2 009,H1,0))))

A B C D E F G H I
J
2001 1 2002 3 2009 10 2011 11 2012 12

The answer would be Column F = 10. For "2012" the answer would be 12.

Thanks.