• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP Assistance with Defined Name

D

Dufton

New Member
Hi All,


I'll set the backdrop of what I have and what result I'm expecting to get:


Excel 2003 SP3 version


Using VLOOKUP to return a numerical value from a range in another worksheet within the same workbook.


_______


I have defined Class as =Background!$B$16:$B$26 (this is a series of text values) - example Fighter, Thief, Cleric, etc.


I have defined Class1 as =PC1!$G$7 (the user chooses from the list of text values from Class) - let's use Thief in the example


I use a formula to calculate a numerical value of say 200000 in cell E9 of the PC1 worksheet.


Here's the question now:


I want to use Class1 (ex. Thief) in a VLOOKUP function to return a numerical value from another "worksheet" reflective of what 200000 represents in the worksheet titled Thief...it doesn't like the Class1 in the VLOOKUP function - see below


=VLOOKUP(E9,Class1!B4:C23,2,TRUE)


A correct syntax will go a long way as this will save alot of IF statements...thanks.

________________
 
Hi, Dufton!

Can you please clarify this points?

a) your workbook has 3 sheets? Background, PC1 and Class1?

b) you have two named ranges? Class and Class1?

c) in the example, Thief is a value from the Class list and a worksheet name too? ("... in the worksheet titled Thief...")

Sorry if I made any mistake in the interpretation. Besides, you may want to upload an example, it'll be useful.

Thanks.

Regards!
 
Hi Dufton ,


I'll tell you what I tried.


I have a table of numeric values in range E5:F12 , in a worksheet labelled Thief.


In a separate worksheet , I have a range named Class1 , which contains the text Thief.


In this same worksheet , I have a numeric value in cell E2.


I use the following formula :


=VLOOKUP(E2,INDIRECT(INDIRECT("Class1")&"!E5:F12"),2,FALSE)


to return a value , which is looked up in the worksheet labelled Thief.


Narayan


P.S. The last parameter to the VLOOKUP function can be made TRUE , depending on what your requirement is.
 
Thank you both SIRJB7 and NARAYANK991 - for the offer of assistance and your answer - it worked perfectly...like WOW - thank you very very much!!!


I could not have done it without your assistance!!!


Warm Regards,


Bill
 
You must log in or register to reply here.
Back
Top