如何基于多个工作表中的列返回列值? [英] 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屋!