Question

I am trying to reference the "Code" in the Target Sheet shown below to the "Code" in the Reference Sheet in order to return the correct location.  The formula is displaying a result however, it continues to randomly assign an answer from the correct column but incorrect row or reference match-up.  More specifically, it seems to be referencing about 2 rows down regardless of the column index at 1, 2 or 3.

For instance, if I put column index 1, it will return a value of ALLA; column index 2, it will return Alexandria, LA, column index 3, it will return Shreveport, LA.

I've tried to manipulate the data as much as possible by range, row etc. but I never get a consistent or correct answer. At one point I changed the range from highlighting columns 1 - 3 to the range of [Origin]1:Location:200 but it only populated the first target row correctly and then it did another random population to the subsequent rows.

This is the formula I used:

=INDEX({Dispatch Stations Range 1- Route Code}, MATCH(Code@row, {Dispatch Stations Range 3 - Code to Location}, 0), 3)

Target Sheet:

Reference/Index Sheet

Please Help!

NikkiGo · Accepted Answer

So, I was able to take it back to the original formulas in excel.  It looks like I had the return column and lookup column interchanged.  Once I flipped them, everything worked out great.  I'm adding the excel reference that helped me out tremendously and here is the new formula that works:

=INDEX({Dispatch Stations Range 1- Route Code}, MATCH(Code@row, {Dispatch Stations Range 3 - Code to Location}, 0))

I hope this helps someone because I looked on almost every Index Match discussion board in the Community to no avail.  This was quick and simple and literally only took about a minute to change.

INCORRECT FORMAT

Correct format