Excel - 具有多个条件的VLOOKUP [英] Excel - VLOOKUP with multiple criteria

问题描述

我目前正在尝试分析由PC和监视器组成的资产清单电子表格。目前我遇到的问题是将PC与其两台显示器相关联。

I'm currently trying to analyse an asset inventory spreadsheet consisting of PC's and Monitors. The problem I have at the moment is associating a PC with its two monitors.

例如，电子表格如下所示：

For example the spreadsheet looks like this:

Sheet1 - PC的列表

Sheet1 - List of PC's

Monitor 1                    Monitor 2  

Employee Name    PC Tag Number      Serial ID     MonTag1 Tag  Serial ID       MonTag2 Tag  Serial ID
John Smith         ABC123456      123456789     ABC123423        5465614         

2 - 所有资产：

Sheet 2 - All assets:

Parent Asset (Tag)  Tag Number    Serial ID         Description       
ABC123456            ABC123423     5465614           24" Monitor
ABC123456            XYZ123424     5456415           24" Monitor

其中父资产（Tag）=计算机条形码

Where Parent Asset (Tag) = Computer Bar-code

我已经对sheet2进行了PC标签号码单元的VLOOKUP，找到连接到该PC的显示器的标签号。然后，我对sheet2做了一个 VLOOKUP ，找到序列号。

I have done a VLOOKUP of the PC Tag Number cell against sheet2 to find the tag number of the monitor attached to that PC. I then did a VLOOKUP of the tag number against sheet2 to find the serial number.

我现在需要做另一个 VLOOKUP 用于Monitor 2信息，我需要公式来查找类似于第一个 VLOOKUP 的父资产，但给出第二个监视器的值在sheet2中（XYZ123424）。

I now need to do another VLOOKUP for Monitor 2 information, I require the formula to look up the parent asset similar to the first VLOOKUP but give the value of the 2nd monitor in sheet2 instead (XYZ123424).

EG

Monitor 1                    Monitor 2  

Employee Name    PC Tag Number      Serial ID     MonTag1 Tag  Serial ID       MonTag2 Tag  Serial ID
John Smith         ABC123456      123456789     ABC123423        5465614       XYZ123424  5456415 

推荐答案

好的，我想不出如何输出我的解决方案，所以这里是一个例子档案： https://app.box.com/s/4j79fu7vd93u0dc1rltg

OK, I couldn't think of how to type out my solution, so here's a sample file: https://app.box.com/s/4j79fu7vd93u0dc1rltg

我不得不说，我倾向于使用INDEX和MATCH来使用VLOOKUP ，所以组合（加上OFFSET）我用于这个解决方案。

I have to say, I tend to prefer using INDEX and MATCH to VLOOKUP, so that combo (plus OFFSET) what I've used for this solution.

希望这是你想到的，请原谅约翰的名字史密斯的同事。我只是想出一些快速的东西，以确保它能与更多的员工和洗牌的设备列表一起工作。 ：D

Hope this is what you had in mind... and please forgive the name of John Smith's Co-worker. I just had to come up with something quick to make sure it'd work with more employees and a shuffled Equipment List. :D

这篇关于Excel - 具有多个条件的VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！