asked on

Excel Index/Match issue

Hi All,
I am trying to bring in a Post Code from one sheet (Sheet2 - imported from outside source) into another sheet (Sheet1).

The formula I am using appears to be correct EXCEPT for the reference to the source of the Post Code column.

My formula (which will go in Sheet1!E2) looks like this =INDEX(Sheet2!A1:D1757,MATCH(1,(Sheet2!B:B=Sheet1!D2)*(Sheet2!C:C=Sheet1!H2),0),Sheet2!1). I think my problem is the reference to Sheet2, Column1 at the end of the formula.

I could, of course, be barking up the completely wrong tree;-P

I have attached the file I am working on if anyone would like to have a look. I assume that not all the results will match and I will get errors but I am trying to automate as much of this as possible before I have to manually enter data

Cheers
PostCode-Match.xlsx

Flyster

See attached for work-around. If you revise Sheet2 by using column E to Concatenate columns B & C and move the Post Code to column F, you can use this VLOOKUP formula:

=IFERROR(VLOOKUP(D2&H2,Sheet2!E2:F1757,2,FALSE),"Not in Database")

You can change the "Not in Database" to any message you want for fields without data.

Flyster
PostCode-Vlookup.xlsx

Ryan Chong

for cell: Sheet1!E2, you can use Array formula such as:

=INDEX(Sheet2!A:A,MATCH(1,(Sheet2!B:B=Sheet1!D2)*(Sheet2!C:C=Sheet1!H2),0),1)

Open in new window

or:

=INDEX(Sheet2!A$1:D$1757,MATCH(1,(Sheet2!B:B=Sheet1!D2)*(Sheet2!C:C=Sheet1!H2),0),1)

Open in new window

but think this may not be the best solution as it slow down the overall excel since your excel got quite a bit of data.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jimcrint

ASKER

Thanks Ryan, perfect for what I need! Also thanks to Flyster for his suggestion!