从具有多个条件的矩阵中检索值
我很难根据列和行的标准从值矩阵中检索值。一位顾客从家中去了某个商店，但还有比所访问的商店距离其家更近的相同商店。我想知道哪个商店位置最近（如果两个商店的距离相同，则名称的最小值即可）。
这是我所拥有的： Sheet1：
| 位置 | 12345 | 12346 | 12347 |
|---|---|---|---|
| A | 5.53 | 8.68 | 18.94 |
| B | 8.72 | 5.47 | 4.73 |
| C | 6.88 | 7.28 | 4.73 |
| 每个值表示从“位置”到邮政编码（列）的距离 |
我想要的是从邮政编码中选择（最小名称）最近的位置（见下文） 表 2：
| 的邮政编码 | 访问过的 | 所需位置 | 所需位置名称 |
|---|---|---|---|
| 12345 | C | 5.53 | A |
| 12346 | A | 5.47 | A |
| 12347 | A | 4.73 | B |
非常感谢任何帮助。干杯!
我试过这个：
=INDEX(Sheet1!$A$2:$A$4,MATCH(1,(Sheet1!$B$1:$D$1=Sheet2!$A2)x(Sheet2!$C2=Sheet1!$B$2: $D$4),0))
但返回 #N/A
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在
Sheet2 C2中使用：=MIN(INDEX(Sheet1!$B$2:$D$4,,MATCH($A2,Sheet1!$B$1:$D$1,0)))在
Sheet2 D2中使用：=INDEX(工作表 1!$A$2:$A$4,MATCH($C2,INDEX(工作表 1!$B$2:$D$4,,MATCH($A2,工作表 1!$B$1:$D$1,0) ))))In
Sheet2 C2use:=MIN(INDEX(Sheet1!$B$2:$D$4,,MATCH($A2,Sheet1!$B$1:$D$1,0)))In
Sheet2 D2use:=INDEX(Sheet1!$A$2:$A$4,MATCH($C2,INDEX(Sheet1!$B$2:$D$4,,MATCH($A2,Sheet1!$B$1:$D$1,0))))