Vlookup from another sheet

**Jafro** · Aug 16th, 2003, 09:01 PM

How do I do a vlookup on a table that is on another excel sheet?
IE What is the formula to go to the other sheet and return the proper answer
Thanks Jafro

**Brian from Maui** · Aug 16th, 2003, 09:15 PM

Originally Posted by Jafro

How do I do a vlookup on a table that is on another excel sheet?
IE What is the formula to go to the other sheet and return the proper answer
Thanks Jafro

If it's in the same workbook something like

=VLOOKUP(A1,SHEET2!B1:C20,2,0), or you could "name" your table and use,

=VLOOKUP(A1,TableName,2,0),

If it's in another workbook, the workbook needs to be open and use the workbook name.

**jzuber** · Aug 16th, 2003, 10:22 PM

Have both workbooks open.

Create the Vlook up
Window to the other file, highlight (or use the range name) of the data you want to search
Finish the vlookup statement in the first file

If you copy the formula down to many rows or across columns, be sure to absolute the appropriate cells.

Good luck.

JAZ

**Jafro** · Aug 17th, 2003, 02:53 PM

Brian:
I tried the one you gave and it does not work. Here is some more info
Look at the result in D30 in the sheet "house" and find in the sheet (in the same wookbook) "tables"
The table is located between cells AB5 and AC45
From this info can you give me the formula?
Thanks Jafro

**Brian from Maui** · Aug 17th, 2003, 02:57 PM

Originally Posted by Jafro

Brian:
I tried the one you gave and it does not work. Here is some more info
Look at the result in D30 in the sheet "house" and find in the sheet (in the same wookbook) "tables"
The table is located between cells AB5 and AC45
From this info can you give me the formula?
Thanks Jafro

=VLOOKUP(HOUSE!D30,TABLES!AB5:AC45,2,0)

**Jafro** · Aug 17th, 2003, 11:37 PM

Help:The formula as stated would not work. I even tried the one given by HELP in excel and it does not work either. I am using excel2000 if that means anything.
This is what I want to happen
Look at Cell D30 in the worksheet call "house"
Lookup the coresponding value in a chart in another worksheet in the same workbook called "tables" via vlookup. Insert this value in cell D30.
If there is no value in cell D30 then the cell shoukd remain blank.

Thanks Jafro

**PaddyD** · Aug 17th, 2003, 11:59 PM

"Lookup the coresponding value in a chart "

you cannot look up data from a chart, only the table that houses that data the chart was built from.

**Yogi Anand** · Aug 18th, 2003, 12:03 AM

Hi Jafro:

It is hard to get clear picture of what you are trying to accompish. Please bear with me for a minute and see if I am getting it right from what you have said. In using the VLOOKUP function in a worksheet (let me Call it Jafro's worksheet),

1. the lookup value is in cell D30 of a worksheet called HOUSE
2. Your LOOKUP values are in a range called TABLE in Jafro's worksheet
3. you want to put result of the VLOOKUP in cell D30 of Jafro's worksheet

Questions ...
a. in what column of TABLE, does the value to be looked up lie
b. is the value being lookep up Text or Numeric
c. how is the data laid out in the lookup column of the table (sorted or unsorted)
d. you have made reference to a Chart in your description -- what is the reference to the Chart about?

I think it might clarify things a lot if you can post your related data using Colo's Cool utility. If the problem is clearly understood, I am sure you will get meaningful help.

**Jafro** · Aug 18th, 2003, 08:40 PM

Sorry for the confusion. I will try again
Workbook called "Servicecalc"
Worksheet inside "Servicecalc" called "House"
Another worksheet inside "Servicecalc" called "Tables"
eg. of "Tables" This sheet contains 7 different tables of numbers
Table #1 in "Tables"
AB AC
5 =15 5 = 17
6 =20 6 = 19
7 =30 7 = 25
8 =40 8 = 35
9 =50 9 = 45

The value in cell D30 is compared to the value in AB5 to 9 and the corresponding value in AC5 to9 is returned to cell D30
Hope this helps
jafro

**Brian from Maui** · Aug 18th, 2003, 09:18 PM

Originally Posted by Jafro

Sorry for the confusion. I will try again
Workbook called "Servicecalc"
Worksheet inside "Servicecalc" called "House"
Another worksheet inside "Servicecalc" called "Tables"
eg. of "Tables" This sheet contains 7 different tables of numbers
Table #1 in "Tables"
AB AC
5 =15 5 = 17
6 =20 6 = 19
7 =30 7 = 25
8 =40 8 = 35
9 =50 9 = 45

The value in cell D30 is compared to the value in AB5 to 9 and the corresponding value in AC5 to9 is returned to cell D30
Hope this helps
jafro

From your example, what would be the expected result for a number in D30? And how would one know which table of the seven to use?