In Excel you can lookup a value using the VLOOKUP formula. Things get tricky if you want to do a lookup based on more than one value, but this is fairly easy in Easy Data Transform.
For example if you have a list of orders with product code and supplier code in separate columns in one sheet:
And you want to lookup the description corresponding to each product code and supplier code pair in another sheet:
You can do this quickly and easily with Easy Data Transform. You just need to create helper columns that concatenate the two columns in each sheet and then do the lookup using the helper columns.
- Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.
- Start Easy Data Transform. Make sure the Auto Run button is pressed in.
- Drag the Excel spreadsheet(s) onto Easy Data Transform. If there is more than one sheet in a spreadsheet, you will be asked which sheets to import.
- You should have 2 pink input items. Drag the input items so the sheet you are using to lookup values is vertically below the other sheet.
- Select the top item and click the Concat Cols button.
- A blue Concat Cols transform will be added. In the Right pane check the product code and supplier code columns and set Delimiter to a character that doesn’t appear in the codes. e.g. ^ (caret). A new column will be added.
- Repeat the above 2 items for the second sheet, using the same delimiter (e.g. ^).
- Select the two Concat Cols items using Ctrl+click (PC)/Cmd+click (Mac) (or by dragging a box around them) and click the Lookup button in the Left pane.
- A blue Lookup transform item is added.
- Ensure that the Lookup item is selected and in the Right pane set the Top lookup column and Bottom lookup column to the newly created helper columns. Then set the Bottom value column to the value you wish to lookup.
- You can then click To File to output the results into a new Excel spreadsheet or other format.
- The output file is then created. There is no need to ‘run’ anything.
You can add a Remove Cols transform if you want to remove any columns (e.g. the new helper columns) before output.
Concat Cols and Lookup are just two of the 66 transforms that Easy Data Transform supports.
Windows Download
Mac Download