CODEWITHSUNDEEP
vbaexcelformattingexcel-2007vlookup
Nupur
Nupur

Reputation: 357

Major formatting issue in Excel - VLOOKUP

I need help with a formatting issue in Excel, which is interfering with the VLOOKUP function in my Excel sheet.

I have two sheets with more than 20,000 column values as lookup, and the same number of values for reference. All the values in both cells are weirdly formatted, some with green triangles at the upper left corner of cells, some are text, etc.

Is there a way in Excel using a macro/VBA to remove or make all formatting similar in both sheets? The reason for VBA is because the person who is going to work with this file needs everything automated and is not familiar with Excel at all. I already have the VLOOKUP function in the cells, I just need to work with the formatting.

Upvotes: 0

Views: 3950

Answers (2)

Andrew
Andrew

Reputation: 7778

Well, I fight with partial lookups this way:

In the items array, I create new empty FIRST column and then place formula

="+"&B2

This will take the content of Cell B2 and add + in the front of it.

When I do vlookup, I add "+" to searcheable value

=VLOOKUP("+"&A6,A:O,2,FALSE)

Therefore, instead of comparing for example Strings and numbers, I compare Strings, by adding "+" in the front.

Another technique, is to kill all formatting: Select whole column, click DATA-TEXT TO COLUMNS-DELIMITED and then DESELECT ALL DELIMITERS. Click Finish. This will clear your formatting.

===========================================================================

This is the VBA solution you asked for:

Call it from Excel

=GetLookup(G2,A:C)

Here is VBA:

Function GetLookup(LOOKFOR As String, RANGEARRAY As Range) As String
  GetLookup = Application.WorksheetFunction.VLookup("+" & LOOKFOR, RANGEARRAY, 3, False)
End Function

Good luck!

Upvotes: 2

mkingston
mkingston

Reputation: 2718

I'm assuming the data type in all of the cells is the same, or you want it to all be the same. The following steps will make the cells a uniform type:

  1. Save your workbook, in case this does not do as you require
  2. Select all cells you wish to be of the same cell type
  3. Press Ctrl+1, on the "Number" tab, select the type you wish these cells to take. Press OK.
  4. Open the VBA editor using Alt+F11
  5. Open the immediate window with Ctrl+G
  6. Type the following: for each cell in selection : cell.formula = cell.value : next cell
  7. Press enter (you may have to wait a few seconds).

If you take this action with the same data type (e.g. choose "Text" for both ranges in step 3) on both your ranges you should be "comparing apples with apples" and your VLOOKUP should function as required.

Hope this helps.

Edit: formatting, clarification

Upvotes: 1

Related Questions