08-15-2019 05:09 AM
08-15-2019 05:09 AM
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?
08-15-2019 05:17 AM
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.
08-15-2019 05:33 AM
08-15-2019 07:08 AM
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.
08-15-2019 10:07 AM
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.