Vlookup with multiple matches in Google Sheets

Question

My data looks like:

id   state   value
1     abc      x
1     bcd      y
1     def      z
2     abc      p
2     bcd      q
2     def      r
3     bcd      m
3     def      n

I am trying to vlookup the value for id 1, 2 and so on, but where the state is not abc. Excel automatically picks the first entry and I tried with if condition on the column but am unable to get it work. I even tried Index Match with an if condition inside match, but that didn't work either.

Is there a way I could do it?

@Scott Craner that does not solve my question – Bruce Wayne Oct 31 '19 at 21:44 — Bruce Wayne, Oct 31 '19 at 21:44
Could you please explain this? I am unable to implement it – Bruce Wayne Oct 31 '19 at 21:55 — Bruce Wayne, Oct 31 '19 at 21:55
Also, gsheets does not recognize the aggregate function – Bruce Wayne Oct 31 '19 at 21:56 — Bruce Wayne, Oct 31 '19 at 21:56
share a copy of your sheet with example of desired output – player0 Oct 31 '19 at 22:04 — player0, Oct 31 '19 at 22:04

score 3 · Accepted Answer · answered Oct 31 '19 at 22:05

3

Use Filter:

=index(filter(C2:C9,A2:A9=1,B2:B9<>"abc"),1)

if you want all that meet the criteria, remove the INDEX wrapper:

filter(C2:C9,A2:A9=1,B2:B9<>"abc")

answered Oct 31 '19 at 22:05

Scott Craner

148,073
10
49
81

score 1 · Answer 2 · answered Oct 31 '19 at 22:08

1

try:

=FILTER(C:C, B:B<>"abc", REGEXMATCH(A:A&"", "1|2"))

or just:

=FILTER(C:C, B:B<>"abc")

for partial abc you can do:

=FILTER(C:C, NOT(REGEXMATCH(B:B, "abc")))

answered Oct 31 '19 at 22:08

player0

124,011
12
67
124

Vlookup with multiple matches in Google Sheets

2 Answers2