Use a single column to index/match in two columns

Question

I want to use a single column to do a lookup in two columns. Example below.

| a |   | c | a | foo |   | c | a | foo |
| b |   | e | c | bar |   | b | a | baz |
| c |   | b | a | baz |
| d |

The first table is the data I'm using to match on.

The second table is the data table that I'm indexing into.

The third table is the result I want.

Where do you run into trouble? Please post the formula you have tried — Daghan, Feb 17 '20 at 13:13
It's pretty unclear what you want. How do you get from a single column with four entries to three columns with two entries? — Darren Bartrup-Cook, Feb 17 '20 at 13:14
Please share a sample sheet illustrating the problem. The tabular data you shared is not enough to answer the question. — MattKing, Feb 17 '20 at 20:20
@DarrenBartrup-Cook There are 2 input tables (1x4, 3x3) with 1 output table (3x2). — user12785973, Feb 17 '20 at 23:49

score 0 · Accepted Answer · answered Feb 17 '20 at 13:28

0

try:

=FILTER(C:E, 
 REGEXMATCH(C:C&"", "^"&TEXTJOIN("$|^", 1, A:A)&"$"), 
 REGEXMATCH(D:D&"", "^"&TEXTJOIN("$|^", 1, A:A)&"$"))

note, it's case sensitive

answered Feb 17 '20 at 13:28

player0

1 Answers1