Excel Tool

Order Online
Our company contact information There you can find frequently asked questions and answers

Home | Products |Purchase | FAQ | Contact Us | Useful Resources

Visual Lookup In Excel (Dictionary Queries)

If you often use the formula VLOOKUP, HLOOKUP, INDEX  to query data. although the functions and formulas to achieve this effect, but it is tedious and time consuming, Excel Tool Box ís Visual Lookup utility can help you accomplish it quickly, When large amounts of Excel data this tool more useful! you don't have to sortbefore processing the data, and then without cell dropdown.
For example, I want to fill (Queries) column 'Store' and 'Stock' based on column 'Model' and 'Size' . See following screenshots:


Tool location: Add-Ins →Excel Tool Box → Visual Lookup: Shortcuts: (Alt + X + X + L )

The sample table: lookup.xlsx

The nature of this software is to create an EXCEL Data Dictionary and then proceed to query (lookup) data:

The Column A and B is Dictionary Catalog (Value); The Column C and D is Dictionary Content (Item).

The Column F and G is Need to Find Content; The Column H and I is Location of Saved Contents.

1. Click button to select the criteria range (Dictionary Catalog (Value)).

2. Select the criteria range (Dictionary Catalog (Value)): Column A and B

3. Click button to select the criteria range (Dictionary Content (Item)).

4. Select the criteria range (Dictionary Content (Item)): Column C and D

5. Click button to select the criteria range (Need to Find Content).

6. Select the criteria range (Need to Find Content): Column F and G

7. Click button to select the criteria range (Location of Saved Contents).

8. Select the criteria range or cell.

Click 'OK' button

Results:


The Left  Lookup

Steps 1 to 4 are the same as the above example.

5. Click button to select the criteria range (Need to Find Content).

6. Select the criteria range (Need to Find Content): Column M and N

7. Click button to select the criteria range (Location of Saved Contents).

8. Select the criteria range or cell.

9. Click 'OK' button

Results:


Visual Horizontal Lookup:

1. Select the criteria range: $A$1:$D$1

2. Select the criteria range: $A$2:$D$3

3. Select the criteria range: $F$1:$I$1

4. Select the criteria range: $F$2

Choose 'HLookUp' ( Horizontal Lookup) radio button and click 'OK' button.

Results:


A plurality of content of a dictionary, the character string adding (Group by Column).

(Join several rows into one cell with separator Comma.)

For example, I want to combine (merge) multiple cells into one cell based on column 'Airways' (Group by Airways). See following screenshots:

1. Select the criteria range: $A$A

2. Select the criteria range: $B$B

3. Select the criteria range: $D$D

4. Select the criteria range: $E$2

5. Select the 'A plurality of content of a dictionary, the character string adding' checkbox.

6. Click 'OK' button.

 

Results:

Another method of achieving

Copyright © 2001-2014 Excel-Tool All Rights Reserved.

Copyright © Excel-Tool All rights reserved