如何基于多个工作表中的列返回列值? [英] How to return a column value based on columns across multiple worksheets?

问题描述

我需要从单独的工作表或工作簿中获取具有两个条件的状态.我有一个ID列，一个Location列和一个Status列(A列(ID)，B列(Loc)，C列(状态)).

I need to get a status with two conditions from separate worksheets or workbooks. I have an ID column, a Location column, and a Status column (column A (ID), column B (Loc), column C (Status)).

满足ID和位置条件后，它应返回该行的状态"列的值.例如.如果这是一个SQL查询:

When ID and location criteria are met, it should return the value of the Status column for that row. E.g. if this were a SQL query:

SELECT Status from Source where IDsource=IDDestination && LocSource=LocDestination

我该怎么做?

我尝试了if(and))公式，但无济于事.

I tried an if(and)) formula but to no avail.

推荐答案

如果将ID和位置"列连接(或连接)到新列中，则可以执行简单的vlookup().

If you concatenate (or join) the ID and Location column in a new column you can do a simple vlookup().

因此，如果您的数据库"表如下所示:

So if your "database" sheet looks like:

ID   | Location | Status    
------------------------
  1  |  Home    |   OK
  2  |  Home    |  Broken
  3  |  Work    |   OK

然后简单地使用以下公式在位置"和状态"之间添加另一列:C1中的=A1&"-"&B1.

Then simple add another column in between Location and Status like this using the following formula: =A1&"-"&B1 in C1.

ID   | Location | ID-Location | Status    
--------------------------------------
  1  |  Home    |   1-Home    |   OK
  2  |  Home    |   2-Home    |  Broken
  3  |  Work    |   3-Work    |   OK

如果您随后需要状态ID 1和位置住宅，则只需执行以下操作: =Vlookup(A2&"-"&B2,Database!C2:D1000,2,FALSE)在另一张纸的单元格C2中.

If you then need the status of ID 1 and Location Home you simply do: =Vlookup(A2&"-"&B2,Database!C2:D1000,2,FALSE) in cell C2 of your other sheet.

如果存在多个组合，则仅返回第一个匹配项.

这篇关于如何基于多个工作表中的列返回列值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！