Vlookup from another sheet

[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

 

[Yogi Anand]

Hi Jafro:

Sorry, some of the questions still need to be answered.

1. What is the value in cell D30 that is to be compared?
2. in which worksheet is this cell D30 (from which we are going to get the value to be compared) located?
3. you want to return the result of the VLOOKUP in cell D30 -- in which worksheet is this cell (where we are going to return the value returned by the VLOOKUP) located.
Please do realize that we can not return the value to the same cell that we are using to get the value to be compared from.

Boy, you did not think it was going to be this hard to provide the needed information -- it is less painful to go to a dentist than this -- Right? -- just kidding -- we will get there now that we are getting all the drills and the hammer and all the other tools at hand.

 

[Jafro]

The expected number in D30 of say 20(AB6) would return the number 19 (AC6) to D30. The formula would say to look at $AB$5:$AC$9. I believe this means look at all numbers in columm AB5 to Ab9 and return the value corresponding to it (20) from columm AC5 to AC9. Hope this helps.
Jafro

 

[Jafro]

One more detail:The value from the lookup table will be returned to another cell in the wooksheet "House" G9 This is also the sheet that D30 is located in. The vlookup tables are located in a worksheet called "Tables" which is also in the workbook containing the 2 worksheet
Workbook=Servicecalc.xls\House
\Tables
Jafro

 

[Yogi Anand]

Hi Jafro:

I think we have finally all the pieces together. Please look at the following simulation -- First the sheet Tables with Table1 in cells AB5:AC9 ...

ServiceCalc.xls
	AA	AB	AC	AD
3
4		TABLE1
5		5	Five
6		20	Twenty
7		15	Fifteen
8		10	Ten
9		25	TwentyFive
10
Tables

and then the sheet House showing cell D30 with the lookup valure and cell G9 that shows the result of the VLOOKUp formula ...

ServiceCalc.xls
	C	D	E	F	G	H
8
9					Twenty
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30		20
31
House

Take a look -- and please tell if it works for you now -- and if not please tell what is the problem -- and then let us take it from there.

 

[Jafro]

Hello Yodi
Sorry for the delay but I was out of town on business(pay the bill collector before the sherriff). The formuls went in and when I press "enter" it says that it cannot find the file.I am assuming it is the "Tables" file that it is looking for to link with. It also makes the following change to the formula
=VLOOKUP(D30,[tables]Tables!$F$5:$g$36,2,0) Why is excel inserting the [tables]
From the sheet that says it cannot find the sheet I can not find it either
I really appreciate his assistance from everyone
Thank Jafro

 

[Yogi Anand]

Hi Jafro:

Let us regroup here now and take stock of things first before we proceed. Let us verify what you have stated earlier ...

1. The workbook you are working with is SeviceCalc.xls
2. Your lookup table is in a worksheet called Tables that is in ServiceCalc.xls and the table covers the range AB5:AC9
3. You want to use the VLOOKUP formula in a worksheet called House, that is also in the workbook ServiceCalc.xls
4. Your lookup value is in cell D3
5. You want to write VLOOKUP formula in cell G9 of the worksheet House

So questions, before we talk about your new, changed formula in your last post, did you use emulate the formulation in my post that was based literally on every detail according to your specification.

If you have not tried that try that first, so you can see how the VLOOKP formula works -- and once you have understood that, then you can try some variations of that formulation.

If you have tried that, and it did not work for you -- what did not work -- did you work with everything you had specified?

You should also consider posting your data and the result you got using Colo's Cool utility, downloadable from MrExcel page -- and then let us take it from there.