Vlookup then OFFSET over and down

Okay

This is a much simplified version, but...

How do I write a function statement that will:

1) Do a vlookup to find a value
2) If it finds the value then offset by 1 row and 12 columns and return the value of that cell
3) If the lookup value is not found then return a value of 0 or ""

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

using the match and offset functions together can get you what you need. Try
this.

Example:
Column B Column C
Row 2 1 A
Row 3 2 B
Row 4 3 C
Row 5
Row 6 2 =OFFSET(B1,MATCH(B6,B2:B4,FALSE),1)

Typing a 1, 2 or 3 in Cell B6 resolves to an A, B or C. the match function
finds which row the number 2 is in, then the offset moves over 1 column to
capture the letter B.

"mendozalaura" wrote:

>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile: http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>
>

Hi!

> This is a much simplified version, but...

And this is the simplified solution! <g>

=IF(ISNA(MATCH(lookup_value,A1:A5,0)),"",OFFSET(A1,MATCH(lookup_value,A1:A5,0),12))

Biff

"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
> http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

You could simply use the Index & Match combination, and get away from using
the volatile Offset function.

Say data list is in A1 to M50.
Lookup value to find is in N1
Lookup value is located ColumnA.
12 columns over would be Column M
1 Row *below* the lookup value would be Match+1

=INDEX(M1:M50,MATCH(N1,A1:A50,0)+1)

And if you wanted the offset to be variable:
With same parameters as above,
But with column to offset entered in N2,
And row to offset entered in N3

=INDEX(B1:M50,MATCH(N1,A1:A50,0)+N3,N2)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mendozalaura" <[email protected]>
wrote in message
news:[email protected]...
>
> Okay
>
> This is a much simplified version, but...
>
> How do I write a function statement that will:
>
> 1) Do a vlookup to find a value
> 2) If it finds the value then offset by 1 row and 12 columns and return
> the value of that cell
> 3) If the lookup value is not found then return a value of 0 or ""
>
>
> --
> mendozalaura
> ------------------------------------------------------------------------
> mendozalaura's Profile:
http://www.excelforum.com/member.php...o&userid=16118
> View this thread: http://www.excelforum.com/showthread...hreadid=398342
>

why are messages repeated so much??

spam?