Lookup formula to populate a column based on a name search/match in another table

1

Looking for formula help.
Table#1 (contacts) has customer contacts with name, email, etc.
Table #2 (payments) has payment information from customers.
The main matching field is names, though its not always 100% match.

I want to search table #1 for name matches, then populate table #2 email field with the matching customer email from tbl#1, so that I can send payment confirmation emails.

Read up, watched a bunch of videos etc but still struggling with the formula.

This is what I have…would appreciate some help!

Lookup(Contacts-tbl1,thisRow.Name-tbl2 ,[E-mail 1 - Value-tbl1])

This may be way off but was trying to follow some formulas I’d seen. I’m wondering if the Find() formula would come into play here but have struggles to structure it.

Thanks!

1 Like
2

Dear Mina,

Please have a look at this:

As you can see the email is now displayed in table2

Feel free to change or explain what you want to solve, me and the other members in the community will be glad to help you out! :handshake:

2 Likes
3

Hi @Jean_Pierre_Traets,
Thanks so much for the response!
I honestly would love a video series on just formulas, seems like there’s so many ways to do them.

Anyways I edited the table to add table 2-2. The names in my payment table are pre-populated and not a lookup (They’re from another source). Is there a search function that could compare those names to table#1 names and return the email if a name match is found?

Thanks!

4

Something like this?

5

Oh my goodness that was it. Thanks!!!
At least I was in the vicinity formula-wise that’s a good feeling :grin:

I did run into a slight problem though that it seems to treat Sandy and SANDY as different due to the capitalization. Any tips on dealing with that?

Otherwise I could reformat in google sheets as an extra step.

6

If you take another look at that example I’ve altered it so it ignores case. No need to change the data in Goggle Sheets. :slight_smile:

/edit - it’s an age-old trick used in programming: just convert both strings you’re comparing to lower (or upper) case before the comparison. :wink:

2 Likes
7

Wonderful @Nick_Milner . Thanks again so much :clap:t5:

1 Like
8

Hey Nick,

Sorry to jump on an older thread but I didn’t want to just past another one of the same kind of question. I took a look at the solution you posted for mina and while it makes sense its not wanting to work for me. I want to do the same thing as she is but for me the tables are in different sections.

I can use various things such as lookup and filter to grab the right information from the tables but then either I get ALL the information (in this case a phone number) or I get all the names listed as a reference.

Any idea how to make it work out so I can search for the number in a column associated with someones row and then pull the number it self in? I am wanting to do something similar with other bits of information as well but not sure if it’s possible or not.

9

Hi Matthew and welcome to the community. :slight_smile:

The location of tables doesn’t affect formulas because all tables and views have to have unique names. You can select an entire table, cut it, and past it into another section (for example) and all formulas referencing it will continue to work.

Can you share a copy of your doc so I can take a look?

Thanks!

10

Hey @Nick_Milner or anyone else in the post here :slight_smile: any chance you could kindly share the doc or the formula you used to figure this out?

I’ve been struggling with it all morning, so it would be great to hear your solution if you’d be kind enough to share!

Thanks so much!!
Ben

11

Dear @Ben_Swanson,

As I haven’t seen lately activity from @Nick_Milner, I assume that this is the answer you are looking for:

12

Thanks very much for sharing this, but unfortunately doesn’t quite help with what I’m looking for. I’ve found something which closer resembles what it is I’m trying to achieve, to consolidate things, here’s a link to that thread and my post which gives a full detail of what I’m looking for here.

If you are able to help with that, it would be great!? Thanks again!