# Lookup Function and an external source

#### NWalsh3000

##### New Member
Hello, Hoping someone can help me figure this out. My Lookup function only works when the source woorkbook is open. I would like to get it to work without opening the source files.

Here is my formula when the source workbook is open

=LOOKUP(D5,'[BT Price Guide.xlsx]Mt Liq Pricing'!A:A:'[BT Price Guide.xlsx]Mt Liq Pricing'!C:C)

When the source workbook is open this formula works.

Here is my formula when the source workbook is closed

=LOOKUP(D5,'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!A:A:'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!C:C)

it returns #ref

how can I fix this?

Thank you!

Last edited:

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### jorismoerings

##### Well-known Member
Hi,

Neither of your formula's should work.
The # REF is caused by the red colon =LOOKUP(D5,'[BT Price Guide.xlsx]Mt Liq Pricing'!A:A:'[BT Price Guide.xlsx]Mt Liq Pricing'!C:C) which is in both of your formula's.

Change the colon to a comma and it should work.

##### MrExcel MVP
[...]Change the colon to a comma and it should work.

If column A is sorted in ascending order. Needs even then precaution if A is text...

#### jorismoerings

##### Well-known Member
If column A is sorted in ascending order. Needs even then precaution if A is text...

I didn't look at the correct use of the function as the OP states
When the source workbook is open this formula works.

Which i doubt cause of the same error with the colon.
If there's no specific reason using for LOOKUP probably better to change LOOKUP into VLOOKUP or INDEX/MATCH.

##### MrExcel MVP
I didn't look at the correct use of the function as the OP states

Which i doubt cause of the same error with the colon.
If there's no specific reason using for LOOKUP probably better to change LOOKUP into VLOOKUP or INDEX/MATCH.

If ascending order sort is true or possible...
Rich (BB code):
``````=IF(LOOKUP(D5,'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!A:A)=D5,
LOOKUP(D5,'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!A:A),
'X:\bigskyf&b\ZZ BEVERAGE TRACKER TEST ZZ\[BT Price Guide.xlsx]Mt Liq Pricing'!C:C)

would be correct and fast.

#### NWalsh3000

##### New Member
Hello All, Thank you for your help, over the last few weeks I have tried various ways to sync my data the way I would like and ultimately have returned to the original method you were helping me with. Well it turns out that that extra colon was the culprit. Thank you Aladin for your help and your formula. It is working very well. I just have one problem i need some help with now. The cell does not auto update when I change values in the source workbook. Is there a way in VBA maybe to just make the entire workbook update upon opening? maybe a macro I could write and assign to a button that would update all formulas. THANK YOU SO MUCH FOR YOUR HELP!

Norm

Replies
4
Views
313
Replies
5
Views
433
Replies
14
Views
584
Replies
4
Views
365
Replies
21
Views
660

1,191,004
Messages
5,984,116
Members
439,872
Latest member
noaman79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back