Need formula help for Excel

[KenCanuck]

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

 

[T. Valko]

Try this:

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

 

[xlmate]

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]

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.

 

[Pete_UK]

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

 

[KenCanuck]

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.

 

[Pete_UK]

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

 

[T. Valko]

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?

 

[xlmate]

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