Need direction on using code instead of VLOOKUP

Need direction on using code instead of VLOOKUP

• veeruu

  There are a couple of ways to do this. I will post a VBA way as a second post. The easier way that uses built in Excel functions is what I call the Double Match Lookup.
  
  It is explained here:
  
  http://www.cpearson.com/excel/lookups.htm
  
  One of the problems is that you may need to nest the double lookup formula in some if then statements so that the cell shows up as blank rather than an error, depending on how clean you want the output to be. Also, if you are dealing with more than a couple of thousand cells, then the double lookup and get kind of slow, especially in a spreadsheet that changes a lot and therefore needs to recalculate frequently.
  
  When I use this I like to use Cell Validation dropdowns for the column headers in the second sheet to which I am outputting data. That way when you want to change the order of the columns you just pull down the dropdown and click the Column Header name you want to be pulled in.
  
  If this is confusing, I could email you an example of post one here if there is some way to do that.
  
  
  
  

• Antonio_I

  Here is a VBA way to do this. I dont have time to test the code, but this is the gist of it. This works better when you are dealing with massive amounts of cells and you dont want the spreadsheet getting bogged down with thousands of formulas.
  
  Summary
  
  Assuming you have two sheets:
  1. INPUT sheet that has the data
  2. OUTPUT sheet that the data is copied to according to the unique id and columnn header
  
  Assume the column headers are in row 1 of the INPUT sheet
  Assume the column headers are in row 5 of the OUTPUT sheet
  Assume the unique id or the value off of which you were doing the vlookup is in column A (1) on both sheets.
  
  The code searches for the column header in row 1 of the INPUT sheet and sets it equal to one of the ***_input_col variables. The code searches for the column header in row 5 of the OUTPUT sheet and sets one of the ***_output_col variables.
  
  Say you are looking up off of a column of stock ticker in column A and you want to paste three columns of data to the second sheet into the columns with the headers Asset Description, Asset Price, and Asset Sector
  
  For example:
  asset_price_output_col = .Find(what:="Asset Price", lookat:=xlWhole).Column
  Finds the column number with the Header Asset Price in it.
  
  Basically, the code finds the column that each header is in, remembers that, and then uses a loop to work through all of the tickers and paste in the appropriate data. The loop is slightly messy and I did not test it, but this should give you a general idear.
  
  
  
  '******************* Find Column Numbers
  'assumes the headers are in row 1
  'Finds the columnn numbers of the columnn from which you are copying data
  With Sheets("Source").Range("1:1")
  asset_description_input_col = .Find(what:="Asset Description", lookat:=xlWhole).Column
  asset_price_input_col = .Find(what:="Asset Price", lookat:=xlWhole).Column
  asset_sector_inputo_col = .Find(what:="Asset Sector", lookat:=xlWhole).Column
  End With
  
  'assumes the Headers are in Row 5
  'Finds the columnn numbers of the columnn you want to paste
  'the data into in the source sheet
  With Sheets("Output").Range("5:5")
  asset_description_output_col = .Find(what:="Asset Description", lookat:=xlWhole).Column
  asset_price_output_col = .Find(what:="Asset Price", lookat:=xlWhole).Column
  asset_sector_output_col = .Find(what:="Asset Sector", lookat:=xlWhole).Column
  End with
  
  'Loops for Copying and Pasting
  number_of_sources = source_range.rows.count
  number_of_outputs = output_range.rows.count
  source_row_number = 2
  output_row_number = 6
  
  For i = output_row_number to number_of_output + output_row_number
  
  current_source_id = Sheets("Output").Cells(i, 1) 'assuming the id is in column 1
  
  
  For j = source_row_number to number_of_sources + 1
  
  With Sheets("Source")
  asset_description = .Cells(j, asset_description_input_col)
  asset_price = .Cells(j, asset_price_input_col)
  asset_sector = .Cells(j, asset_sector_input_col)
  End with
  
  If current_source_id = Sheets("Input").Cells(source_row_number, 1) Then'assuming the id is in column 1
  
  'pastes the data into the the output sheet
  With wkbModel.Sheets("Output")
  .Cells(i, asset_description_output_col) = asset_description
  .Cells(i, asset_price_output_col) = asset_price
  .Cells(i, asset_sector_output_col) = asset_sector
  End with
  
  Next j
  
  Next i
  
  
  
  
• Need direction on using code instead of VLOOKUP