Forum Discussion
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
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.
9 Replies
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.
Nabil Mourad
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.
I agree my friend
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.
- 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
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.
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
Nabil Mourad
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?
My Excel is in German; SVERWEIS is VLOOKUP; PLZ stands for ZIP Code; WENN is IF
