Index & Match to lookup value from another workbook

[Jamesraywebber85]

I have a template that I would like to put our "Customer ID" in Workbook("TSA Request").Sheets("TSA Request").Range("F5") & in cell F6 I would like for it to look in a workbook called "Customer Data" in column F it shows the Customer ID # & in Column AP has that order # that I want to be returned to cell "F6" in my workbook. Any help would be greatly appreciated. I am stressed to the max trying to figure this one out!

Please Login or Register  to view this content.

I also want to add that the workbook called "Customer Data" would be a closed workbook.

That is the formula I am using but returns #N/A

[Jamesraywebber85]

Here is my template workbook to look from!

[Sam Capricci]

Can't tell with certainty because the file you posted is going against a file you didn't post. BUT, you are using a later version of excel (2007 or later - xlsm) to access an earlier version of excel (2003 or earlier - xls).

[Jamesraywebber85]

I was able to add the data connection to the source. I want to type in the Customer ID number in Worksheets("TSA Request").Range("F5") and the sheet would find that number in Worksheets("Customer Data").Range(P:P) then return in cell Worksheets("TSA Request").Range("F6") the order number from Worksheets("Customer Data").Range(AP:AP). Any help would be greatly appricated.

I believe a Index/Match formula would work but I keep getting N/A in the box.

[Pete_UK]

Cell F5 in the TSA Request sheet is formatted as a number - are you sure that the values that you are looking for in column AP of the Customer Data file are also numbers - could they be text values that look like numbers?

From your description above, I think you might have things the wrong way round in your formula, which is:

=INDEX('F:\Call Center\Harrison\Rays\[Customer Data.xls]Sheet1'!$F$1:$F$65536,MATCH(F5,'F:\Call Center\Harrison\Rays\[Customer Data.xls]Sheet1'!$AP$1:$AP$65536,0))

This is looking for a match in column AP of the Customer Data file, and if found it will return data from column F, which is not what you put in your description.

Hope this helps.

Pete