Excel Index/Match?

[kmolls]

Here is the problem.
I have a shipping spreadsheet with an address and 8 tracking numbers for each address all in one line. Each tracking number is in it's own column.

I want to be able to enter in a tracking number (the last 8 digits actually), and return the address. I cannot seem to get Index & Match to search through all 8 columns of tracking numbers.

Here is the code I'm working around.
=INDEX(PHASE3!A:A,MATCH("1zx8754v03"&C13,PHASE3!G:N,0))

I am one indexing one page "Phase3" outside of the page I want to enter and receive data.

Returning data in Column A from Phase3.
In reference to "1zx8754v03"&C13.
Searching in PHASE3!G:N
Exact Match.

It will work if I just search one column PHASE3!G:G, but I want to search G:N

Any help??

 

[Haseeb Avarakkan]

Hello, Welcome to MrExcel

Try, Control+Shift+Enter, not just Enter,

=INDEX(PHASE3!A:A,MIN(IF(PHASE3!G2:N1000="1zx8754v03"&C13,ROW(PHASE3!G2:N1000))))

Do not use the hole column reference If your data is updating, use a dynamic range.

 

[kmolls]

Thanks, I did try S+C+enter previously, didn't do anything else.
I also had tried fixing the data range.

I plugged in your code, it is entering data, but not from the correct line.

It actually plugs in data without entering any data into C13.

I will check it out further though.

Any other suggestions?

 

[Haseeb Avarakkan]

It actually plugs in data without entering any data into C13.

That because, If you don't have any matching values in PHASE3!G:N MIN will give zero, so INDEX will pull the first cell from the array, here is PHASE3!A:A, that is A1.

You can use an IF statement like, =IF(C13="","",INDEX(...) or use SMALL instead of MIN.