Retrieve a value from a matrix with multiple criteria

Question

I am having difficulty retrieving a value from a matrix of values based on criteria on columns and rows. A customer has gone to a store location from their home but there are same stores closer to their home than the one visited. I want to know which store location was the closest (if two have the same distance, min of name would do).

Here is what I have: Sheet1:

Location	12345	12346	12347
A	5.53	8.68	18.94
B	8.72	5.47	4.73
C	6.88	7.28	4.73
Each value represents distance from 'Location' to a zip code (columns)

What I want is to choose the (min of name) closest location from the zip code (see below) Sheet2:

Zip	Location Visited	Desired Location Dist.	Desired Location Name
12345	C	5.53	A
12346	A	5.47	A
12347	A	4.73	B

Any help is greatly appreciated. Cheers!

I tried this:

=INDEX(Sheet1!$A$2:$A$4,MATCH(1,(Sheet1!$B$1:$D$1=Sheet2!$A2)x(Sheet2!$C2=Sheet1!$B$2:$D$4),0))

but returns #N/A

Answer 1

In Sheet2 C2 use: =MIN(INDEX(Sheet1!$B$2:$D$4,,MATCH($A2,Sheet1!$B$1:$D$1,0)))

In Sheet2 D2 use:

=INDEX(Sheet1!$A$2:$A$4,MATCH($C2,INDEX(Sheet1!$B$2:$D$4,,MATCH($A2,Sheet1!$B$1:$D$1,0))))