Find reference name across two different sheets and pull forward to 3rd sheet

jcouncil · 09/05/22

I have a reference request sheet that will log Approvals. Once approved, Customer reference name is entered manually. I want to then look for that name across two sheets and when matched, pull in 3 fields of information - name, email, phone.

I started with this formula, which works - but only when searching in 1 of the 2 Reference Libraries (Enterprise only)

=(VLOOKUP([Customer Reference]@row, {Enterprise Reference Library Range 2}, 5, false))

I tried to add in the 2nd reference library using this formula, but it returns error: #UNPARSEABLE

=IF([Sales Team] = Enterprise, (VLOOKUP([Customer Reference]@row, {Enterprise Reference Library Range 2}, 5, true)), (VLOOKUP([Customer Reference]@row, {Provider Reference Library Range 1}, 6, TRUE))

Any help?

Genevieve P. · 09/09/22

Hi @jcouncil

A few things to note:

When you're looking for text in a formula, you'll need to use Quotes around the word so the formula knows where the text starts and ends. For example, "Enterprise".

Secondly, I see you have a column reference of [Sales Team] but no row reference. You'll want to add @row after the column name to ensure it looks for "Enterprise" in this current row.

Try:

=IF([Sales Team]@row = "Enterprise", VLOOKUP([Customer Reference]@row, {Enterprise Reference Library Range 2}, 5, true), VLOOKUP([Customer Reference]@row, {Provider Reference Library Range 1}, 6, TRUE))

Cheers,

Genevieve

Genevieve P. · 09/09/22