Matching two columns in one sheet to two columns i

问题:

I need to assign a status to a row based on a VLOOKUP query between two worksheets. The problem is that the identifier is not always unique. However, the identifier + a date value should be unique. I wanted to use:

=VLOOKUP(A3&H3,'OtherSheet'!D:E,1,FALSE)  

with A3 being the identifier and H3 being the corresponding date. D in the other sheet is the identifier and E is the date column. However, I keep getting #N/A.

Does this mean that there are no matches with the "identifier+date" or is Excel looking for "identifier+date" in either column D or E? If the latter is true, how can I let Excel concatenate D and E when matching to the search pattern?

回答1:

There's work around without using CTRL+Shift+Enter.

Use this formula that will match A3 in D column of othersheet and H3 with the date in column E of the othersheet.

=INDEX(OtherSheet!F:F,MATCH(1,INDEX((OtherSheet!D:D=A3)*(OtherSheet!E:E=H3),),0))

The formula will return data from F column of OtherSheet. You can modify the range OtherSheet!F:F as appropriate.

回答2:

That formula is looking to find A3 concatenated with H3 (identifier&date) in OtherSheet ColumnD that contains only identifiers, so will inevitably fail. Yes, Excel is looking for “identifier+date” in column D.

Excel will happily concatenate A3 with H3 ‘on the fly’ (within a formula) but will not so happily concatenate OtherSheet ColumnD and ColumnE values in the same way. The conventional solution, because usually simplest in a case like this, is to prepare for the VLOOKUP by adding a helper column that concatenates the D and E values while preserving these in the same row as the value sought.

Because VLOOKUP will only look to the right this is usually a column that is added to the left of the value being searched for, so say either in C or by insertion of a column immediately to the right of C. However, since you are only checking a single column the location is not critical. You might add this (in OtherSheet) as ColumnZ, with a formula such as:

=D2&E2  

copied down to suit*. Again because you are only checking a single column it does not matter which row such a formula is placed in.

However, because only checking whether A3&H3 exists in OtherSheet a simple alternative may be to apply COUNTIFS:

=COUNTIFS(OtherSheet!D:D,A3,OtherSheet!E:E,H3)

Any result other than 0 from this should indicate that the combination being tested for exists in OtherSheet – without need for a helper column.

* Depending on the format of your identifiers it is possible that concatenation may introduce ambiguity. For example ID90 concatenated with 11/1/15 may not be distinguishable from ID901 concatenated with 1/1/15, so it may be advisable if taking this approach to introduce a delimiter, in both the VLOOKUP formula (say A3&"|"&H3 rather than just A3&H3) and therefore also in the helper column, say =D2&"|"&E2.

回答3:

You likely would want to use Index/Match instead. Vlookup is tricky when it comes to searches for multiple things. Here's the way you would use Index/Match:

Without knowing how your spreadsheet is set up, here's how you could do it:

If I understand correctly, you want to use A3 to find the match in OtherSheet!D, and H3's match in OtherSheet!E. Index match is perfect for this. Instead of vLookup, use

=Index(OtherSheet!D:D&","&Text(OtherSheet!E:E,"mm-dd-yyyy"),Match(A3&H3,OtherSheet!D&OtherSheet!E,0)), and enter with CTRL+SHIFT+ENTER.

What the Index() will return is the concatenated Identifier and Date, separated with a comma. If, though, you have a table like this:

That index/match formula will return "Batman". The index to return is the named range G2:G5. You're looking for a match on A1 (the Identifier) and B1 (the Date), then you're searching for (in the order you just put) the Identifier to be in the range E2:E5, and the Date to be in F2:F5. When there's a match for both, it returns the name in G2:G5.

Here's a link to a site on using Index/Match, and another and its advantages over vlookup.