Need to enter information into a cell, using a reference list on a separate excel file

[aha01]

Hi

I have a list (File B) where I want to fill in the location area in a column right next to the suburb, depending on the suburb. Means there are several suburbs with the same location area.

The reference list (File A) is on a separate excel file and I'm not sure if that is a problem, otherwise I also could copy the reference list into the existing file in a new tab/sheet

I tried lookup and index formula but couldn't make it work ;)

Thanks for your help

[#2]

Hi,

No matter if the lookup table is in the same workbook, or in another one.

For now, you can depend on this formula to lookup the location from the File A:

=INDEX('[File A.xlsx]Sheet1'!$A$2:$A$10,MATCH(C2,'[File A.xlsx]Sheet1'!$B$2:$B$10,0))

But please note that this formula depends on the suburb as a lookup key, and this is not a good way!

Because the suburb may have duplicate values in the future, so I advise you to create a new column for the Work ID in the left-most of the reference table so that you depend on it in the future.

And this will make you able to use VLOOKUP function which is easier and most straightforward than  INDEX & MATCH.

Regards

[#3]

Thanks Haytham, that's exactly what I need and now I see the formula I understand how it works and what it refers to -great help :)

Anita