Referencing information between two sheets

[LukeM82]

In the attachment you will see that Sheet1, Col A has Store Codes assigned to various Order #s (Column B). In Sheet2 the "Store Code" column is blank with a list of the same Order #s in Col B. Is there a formula that will quickly fill in the information in the "Store Code" column in Sheet 2 by referencing the information in Sheet1? I need to assign the correct "Store Codes" to their respective "Order #" in Sheet2.

Any help is appreciated.

Thank you,
-Luke

[AliGW]

In A2 on Sheet 2 and copied down:

=INDEX(Sheet1!A2:A195,MATCH(Sheet2!B2,Sheet1!B2:B195,0))

[LukeM82]

Thank you for your response! When I used that formula I am getting a lot of #N/A results (See attached). Do you know how to fix this?

[Kymba]

Is this any use to you, in A2 Sheet2 copied down?

=IFERROR(INDEX(Sheet1!A2:A195,MATCH(Sheet2!B2,Sheet1!B2:B195,0)),"")

That should replace all NA's with a blank.

[AliGW]

The #N/A results show that there is no match. You can do what Kymba has suggested:

=IFERROR(INDEX(Sheet1!A2:A195,MATCH(Sheet2!B2,Sheet1!B2:B195,0)),"")

or something like this:

=IFERROR(INDEX(Sheet1!A2:A195,MATCH(Sheet2!B2,Sheet1!B2:B195,0)),"No Store")

[LukeM82]

The issue is though, there are matches in Sheet1 for all of the #N/A results. For example, B8 in Sheet2 should be returning "Wolf Lakes" from A4 in Sheet1.

[AliGW]

Sorry - my error. You need to fix the ranges thus:

=IFERROR(INDEX(Sheet1!$A$2:$A$195,MATCH(Sheet2!B2,Sheet1!$B$2:$B$195,0)),"")

before copying down.