如何在不使用INDEX()与MATCH()时，基于双条件从三列中获取pstatus值

Got it, let's figure out how to pull the previous month's status for a matching ID without relying on INDEX and MATCH. First, let's use a sample data set to make this tangible—say your spreadsheet has these columns (A, B, C):

We want to grab, for example, the Jan status for ID 101 when we're looking at Feb, or dynamically pull the prior month's status for any given ID.

Option 1: Use TEXTJOIN with Conditional Checks (Great for Text Statuses) #

If your status values are text (like "Active" or "Paused"), TEXTJOIN paired with IF conditions works perfectly (this works in Excel 2019, 365, and newer versions):

To get the Jan status for ID 101:

=TEXTJOIN("", TRUE, IF((A:A=101)*(B:B="Jan"), C:C, ""))

If you want it dynamic (e.g., get the previous month's status relative to the current row's month), adjust the month condition. Let's say column B uses numeric months (1=Jan, 2=Feb), then for row 2 (ID 101, Feb):

=TEXTJOIN("", TRUE, IF((A:A=A2)*(B:B=B2-1), C:C, ""))

How This Works: #

• (A:A=101) filters rows where the ID matches our target.
• (B:B="Jan") narrows it down to the previous month (adjust this if you use dates—e.g., EDATE(B2, -1) to get the prior month's date).
• The IF function returns the status only if both conditions are met, otherwise an empty string.
• TEXTJOIN glues together all non-empty results (since we use TRUE to skip blanks) with no separator, giving us the exact status we need.

Option 2: VLOOKUP with a Helper Column #

If you don't mind adding a helper column, this is a straightforward approach:

1. Add a new column (say D) with a combined key: =A2&"|"&B2 (this merges ID and month with a separator like | to avoid conflicts).
2. Now, to look up ID 101's Jan status:

=VLOOKUP(101&"|"&"Jan", D:C, 2, FALSE)

For dynamic previous month lookup with date values in column B:

=VLOOKUP(A2&"|"&TEXT(EDATE(B2, -1), "mmm"), D:C, 2, FALSE)

This works because we're searching for the combined ID+previous-month key in the helper column, then returning the corresponding status from column C.

Both methods skip INDEX and MATCH entirely while reliably pulling the target value based on your two conditions (ID match + previous month).

内容的提问来源于stack exchange，提问作者vitthal