VLOOKUP for blanks in column

Question

I am a new Alteryx user and am having trouble filling in blanks of a column using a vlookup to another file. I have data in the "Customer3XLevel" column of my "Units" file that has data but also blanks. I would like to fill in the blanks using a lookup to another table. When there are blanks in the "Customer3XLevel" column, there is a value in the "Customer" column that can be used as the lookup value. I would ultimately like to maintain all data in my "units" file and simply fill in the blanks using the lookup value in the "customer" column against another file.

Here is how the vlookup would look in Excel:

TUSHAR050392 · Answer

Hey, I will advise to use Find and Replace tool which is works like vlookup. F input will be your first sheet and R will be your second sheet. You will then define the common or lookup column which will be customer and field1 respectively. Then select append fields to record and choose the Field14 that you need the values from. This will bring matched values in the output as a column named Field14.

Once that is done you can use a formula tool to put a condition that If isnull(Customer3XLevel) then field14 else Customer3XLevel end. This way your column Customer3XLevel will get filled and then you can drop Field14 column using select tool.

Hope this solution is helpful. Please mark it as solution if it is.

apathetichell · Answer

In SQL and in everything in the known universe outside of Excel what you are trying to do is refered to as a JOIN. you join one table -> another table on a field - in your case customer (or customer id) - one more thing. If you MULTIPLE VALUES in both of your data sources you will get a record for each multiple value combo in your output. This is not how Excel works because it uses evil magic. But everything else follows this logic.