Need formula help for Excel

K

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
 
X

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
 
K

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.
 
P

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
 
K

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.
 
P

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

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?
 
X

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top