Google Sheets formula to compare data in a single cell with multiple cells and return a value based on those cells

0
votes

Currently, I have sheets document that compares cell name containing a last name field with a column of data elsewhere and returns a third column's value when it matches. I have posted an example below

i.e.

Here is the formula ||| Lookup(A1,'Sheet 2'!B:B,'Column C'!C:C)

Sheet One with Formula              Sheet 2:
Column A    Column B                Column A    Column B    Column C
Smith       111111                  Oscar       Smith       111111

The problem is if there are 2 smiths it only grabs the first one.

What I want to do is use the first initial. last name in sheet one and compare to column A and B to return Column C. 

Sheet One with Formula              Sheet 2
Column A    Column B                Column A    Column B    Column C
O. Smith    111111                  Alex        Smith       222222
                                    Oscar       Smith       111111

I am stuck... Here is a link to help out. https://docs.google.com/spreadsheets/d/1m087VIKk_F8OUbKy2wMvb12DM9fg9r0YIlbU24QHsSY/edit?usp=sharing

2
google-sheetsformulalookup
I tried adding a third column in sheet two, to concatenate the two names into the item I was searching for, but the lookup failed for some reason. – Mike Wilday

2 Answers

1
votes

In B3 of Sheet 1 I entered this formula

=ArrayFormula(if(len(A3:A), vlookup(A3:A, {Left(Sheet2!A2:A)&". "&Sheet2!B2:B, Sheet2!C2:C}, 2, 0),))

See if that works for you ?

0
votes

JPV also informed me that Lookup requires content to be in alphabetical order. If I would have used Vlookup I could have pulled the data using the concatenation method I was trying out.

Concatenation formula: =Left(A2,1)&". "&B2

Working Formula - vlookup(A1,'Sheet 2'!B:B,'Column C'!C:C)

Sheet One with Formula      Sheet 2:
Column A    Column B        Column A    Column B    ColumnC     Column D
A. Smith    222222          Oscar       Smith       O.Smith     111111
                            Anthony     Smith       A. Smith    222222