I have 2 worksheets. I need to have a formula that reads cell (Ex.) A4 in
Sheet 1 then references Sheet 2 to match that text out of a list (B2-B10) and
supply the corresponding value in (C2-C10). This result (the formula) will
be in cell B4 on sheet 1. I thought it was a MATCH function but I can't get
it to work.

Thanks! - Ken

Try this:

=VLOOKUP(A4,Sheet2!B$2:C$10,2,0)

--
Biff
Microsoft Excel MVP


"KenCanuck" wrote in message
...
I have 2 worksheets. I need to have a formula that reads cell (Ex.) A4 in
Sheet 1 then references Sheet 2 to match that text out of a list (B2-B10)
and
supply the corresponding value in (C2-C10). This result (the formula)
will
be in cell B4 on sheet 1. I thought it was a MATCH function but I can't
get
it to work.

Thanks! - Ken

Got #REF

I'll try explain again, I probably wasn't clear.

Sheet 1: cell A4. Has drop down list of names (Pat, Jim, Bob)
Sheet 2: Cells B1:B3 has names Pat, Jim, Bob
Sheet 2: Cells C1:C3 has ages 33, 24, 47

I want Cell B4 in Sheet 1 to lookup and match the name and show as a result
the corresponding age.

Hope this makes sense. - Thanks again.

"T. Valko" wrote:

Try this:

=VLOOKUP(A4,Sheet2!B$2:C$10,2,0)

--
Biff
Microsoft Excel MVP


"KenCanuck" wrote in message
...
I have 2 worksheets. I need to have a formula that reads cell (Ex.) A4 in
Sheet 1 then references Sheet 2 to match that text out of a list (B2-B10)
and
supply the corresponding value in (C2-C10). This result (the formula)
will
be in cell B4 on sheet 1. I thought it was a MATCH function but I can't
get
it to work.

Thanks! - Ken

I probably wasn't clear.

No, you were very clear!

If you're getting a #REF! error make sure you use the correct sheet name and
the correct syntax to define the sheet name in the formula. What is the
*exact* sheet name of sheet 2?

--
Biff
Microsoft Excel MVP


"KenCanuck" wrote in message
...
Got #REF

I'll try explain again, I probably wasn't clear.

Sheet 1: cell A4. Has drop down list of names (Pat, Jim, Bob)
Sheet 2: Cells B1:B3 has names Pat, Jim, Bob
Sheet 2: Cells C1:C3 has ages 33, 24, 47

I want Cell B4 in Sheet 1 to lookup and match the name and show as a
result
the corresponding age.

Hope this makes sense. - Thanks again.

"T. Valko" wrote:

Try this:

=VLOOKUP(A4,Sheet2!B$2:C$10,2,0)

--
Biff
Microsoft Excel MVP


"KenCanuck" wrote in message
...
I have 2 worksheets. I need to have a formula that reads cell (Ex.) A4
in
Sheet 1 then references Sheet 2 to match that text out of a list
(B2-B10)
and
supply the corresponding value in (C2-C10). This result (the formula)
will
be in cell B4 on sheet 1. I thought it was a MATCH function but I
can't
get
it to work.

Thanks! - Ken

try this in B4

=VLOOKUP(A4,Sheet2!B2:B10,2,0)

to ignore ISNA when there is no match, use this
=IF(ISNA(VLOOKUP(A4,Sheet2!B2:B10,2,0)),"",VLOOKUP (A4,Sheet2!B2:B10,2,0))

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.

Thank You

cheers, francis



"KenCanuck" wrote:

I have 2 worksheets. I need to have a formula that reads cell (Ex.) A4 in
Sheet 1 then references Sheet 2 to match that text out of a list (B2-B10) and
supply the corresponding value in (C2-C10). This result (the formula) will
be in cell B4 on sheet 1. I thought it was a MATCH function but I can't get
it to work.

Thanks! - Ken

A few typos, Francis:

=VLOOKUP(A4,Sheet2!B2:C10,2,0)

and:

=IF(ISNA(VLOOKUP(A4,Sheet2!B2:B10,1,0)),"",VLOOKUP (A4,Sheet2!
B2:C10,2,0))

Hope this helps.

Pete

On Feb 24, 7:00*pm, xlmate wrote:
try this in B4

=VLOOKUP(A4,Sheet2!B2:B10,2,0)

to ignore ISNA when there is no match, use this
=IF(ISNA(VLOOKUP(A4,Sheet2!B2:B10,2,0)),"",VLOOKUP (A4,Sheet2!B2:B10,2,0))

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you. *

Thank You

cheers, francis



"KenCanuck" wrote:
I have 2 worksheets. I need to have a formula that reads cell (Ex.) A4 in
Sheet 1 then references Sheet 2 to match that text out of a list (B2-B10) and
supply the corresponding value in (C2-C10). *This result (the formula) will
be in cell B4 on sheet 1. *I thought it was a MATCH function but I can't get
it to work.

Thanks! - Ken- Hide quoted text -

- Show quoted text -

Hi Pete

Thanks.


--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis










"Pete_UK" wrote:

A few typos, Francis:

=VLOOKUP(A4,Sheet2!B2:C10,2,0)

and:

=IF(ISNA(VLOOKUP(A4,Sheet2!B2:B10,1,0)),"",VLOOKUP (A4,Sheet2!
B2:C10,2,0))

Hope this helps.

Pete

On Feb 24, 7:00 pm, xlmate wrote:
try this in B4

=VLOOKUP(A4,Sheet2!B2:B10,2,0)

to ignore ISNA when there is no match, use this
=IF(ISNA(VLOOKUP(A4,Sheet2!B2:B10,2,0)),"",VLOOKUP (A4,Sheet2!B2:B10,2,0))

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.

Thank You

cheers, francis



"KenCanuck" wrote:
I have 2 worksheets. I need to have a formula that reads cell (Ex.) A4 in
Sheet 1 then references Sheet 2 to match that text out of a list (B2-B10) and
supply the corresponding value in (C2-C10). This result (the formula) will
be in cell B4 on sheet 1. I thought it was a MATCH function but I can't get
it to work.

Thanks! - Ken- Hide quoted text -

- Show quoted text -

Hi, thanks for the response, I got #REF

I'll try explain again, I probably wasn't clear.

Sheet 1: cell A4. Has drop down list of names (Pat, Jim, Bob)
Sheet 2: Cells B1:B3 has names Pat, Jim, Bob
Sheet 2: Cells C1:C3 has ages 33, 24, 47

I want Cell B4 in Sheet 1 to lookup and match the name and show as a result
the corresponding age.

Hope this makes sense. - Thanks again.

"xlmate" wrote:

try this in B4

=VLOOKUP(A4,Sheet2!B2:B10,2,0)

to ignore ISNA when there is no match, use this
=IF(ISNA(VLOOKUP(A4,Sheet2!B2:B10,2,0)),"",VLOOKUP (A4,Sheet2!B2:B10,2,0))

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.

Thank You

cheers, francis



"KenCanuck" wrote:

I have 2 worksheets. I need to have a formula that reads cell (Ex.) A4 in
Sheet 1 then references Sheet 2 to match that text out of a list (B2-B10) and
supply the corresponding value in (C2-C10). This result (the formula) will
be in cell B4 on sheet 1. I thought it was a MATCH function but I can't get
it to work.

Thanks! - Ken

Try this:

=VLOOKUP(A4,'Sheet 2'!B1:C3,2,0)

Ensure that the sheet name is exactly as it appears on the sheet tab,
including any spaces - apostrophes are needed if you have spaces in
the sheet name.

Hope this helps.

Pete

On Feb 24, 7:33*pm, KenCanuck
wrote:
Hi, thanks for the response, I got #REF

I'll try explain again, I probably wasn't clear. *

Sheet 1: *cell A4. *Has drop down list of names (Pat, Jim, Bob)
Sheet 2: Cells B1:B3 has names Pat, Jim, Bob
Sheet 2: Cells C1:C3 has ages 33, 24, 47

I want Cell B4 in Sheet 1 to lookup and match the name and show as a result
the corresponding age.

Hope this makes sense. - Thanks again.



"xlmate" wrote:
try this in B4

=VLOOKUP(A4,Sheet2!B2:B10,2,0)

to ignore ISNA when there is no match, use this
=IF(ISNA(VLOOKUP(A4,Sheet2!B2:B10,2,0)),"",VLOOKUP (A4,Sheet2!B2:B10,2,0))

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you. *

Thank You

cheers, francis

"KenCanuck" wrote:

I have 2 worksheets. I need to have a formula that reads cell (Ex.) A4 in
Sheet 1 then references Sheet 2 to match that text out of a list (B2-B10) and
supply the corresponding value in (C2-C10). *This result (the formula) will
be in cell B4 on sheet 1. *I thought it was a MATCH function but I can't get
it to work.

Thanks! - Ken- Hide quoted text -

- Show quoted text -