How to return a column value based on columns acro

问题:

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)).

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

How can I accomplish this?

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

回答1:

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

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

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.

If multiple combinations exist, only first match is returned.

回答2:

An array formula like the following would do:

{=OFFSET(C:C;MIN(IF((A:A="Some ID")*(B:B="Some Location");ROW(C:C);""))-1;0;1;1)}

It gets a little heavy, but if you can limit row count, it gets faster:

{=OFFSET(C:C;MIN(IF((A2:A1001="Some ID")*(B2:B1001="Some Location");ROW(C2:C1001);""))-1;0;1;1)}