• 516K Members
• 7,088 Online
• 613K Conversations
SOLVED

New Contributor

# Excel Combination of IF and VLOOKUP

Hello,

I want to connect the If formula and VLOOKUP formula.

I have a sheet in which not every zip code is present but the Citys are.

I'm filling the gaps using a VLOOKUP and a second sheet with postal codes and citys as the matrix

But since most citys have more than one zip code I don't want to drag the formula over the entire Data set. (That would change the present postal code to the one in my postal code sheet)

I had the Idea that I could combinate the VLOOKUP with an IF Formula. (IF cell is empty use VLOOKUP IF not keep Value)

Is that possible and If so how would that formula be?

Best regards

9 Replies

# Re: Excel Combination of IF and VLOOKUP

Hi

Yes of course you can but instead of reinventing the sheet, would you please share either a sample Excel data or a snap shot with column Letters & Row Numbers to facilitate customizing the formula for your situation.

Good Luck

Highlighted
Solution

# Re: Excel Combination of IF and VLOOKUP

As variant you may generate summary table with Power Query.

If with formulas you can't replace current value by formula. If only to add one more column where to put current or vlookup-ed value.

# Re: Excel Combination of IF and VLOOKUP

My Excel is in German; SVERWEIS is VLOOKUP; PLZ stands for ZIP Code; WENN is IF

# Re: Excel Combination of IF and VLOOKUP

Nabil, if I understood the logic correctly, that's

<cell value>=if <cell value>="" then <another value> else <cell value>

How that could work with formulas not considering iterative calculations?

# Re: Excel Combination of IF and VLOOKUP

That would challenge my ongoing process, I'm using the Excel sheet later in combination with a Word text to generate series letters which I post as ads in job portal. I don't think I can fill in the postal code from two cell sources

# Re: Excel Combination of IF and VLOOKUP

With mail merge you may ignore your current PLZ column and use added one, let say PLZfilled, into which you add the post code from PLZ if not empty, otherwise the value returned by VLOOKUP.

# Re: Excel Combination of IF and VLOOKUP

I hear you Sergei, The issue also is: for each City we may have multiple Zip codes while each Zip code corresponds to one city.It wasn't clear to me weather the lookup value for @Freezen  will be the City or the Zip Code, that's why I asked for a sample. I was considering a helper column as well.

On the other hand, I have a preference to the Power Query option but I always noticed (in my courses) that clients are fearful from PQ and have a tendency to Formulas... I don't know why? is it because formulas update without Refresh? may be!

Have a great day everyone.

# Re: Excel Combination of IF and VLOOKUP

Nabil,

Power Query is a great tool, but that always a balance. Refresh is one point. Another one - people usually tend to combine data source, data preparation and reporting in one place, Power Query is more oriented on their separation (as it shall be). Formulas are more universal, in mixed Windows/Mac/mobile/Online environment Power Query is less preferable.

And I guess the main point one shall invest some time to understand how Power Query works and at least basic of M-script. With formulas you may copy/paste the pattern, adjust to your ranges and use it. Not so simple with Power Query if you never used it.

# Re: Excel Combination of IF and VLOOKUP

I agree my friend

Related Conversations
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
vlookup using concatenate function and helper column
Angela McGhin in Excel on
4 Replies
Vlookup doesn't work for inexplicable reason
Domonkos in Excel on
2 Replies
vlookup
BowlingAlleyBob in Excel on
3 Replies