Multiple results using lookup value form 2 column

E

excel_1317

Board Regular
Joined
Jun 28, 2010
Messages
212
In Below table, Column A has company names and column B has IDs. Column C and Column D contains Lookup Values which i need to lookup in Column B.

Here is a code that needs to be amended so that it takes columns C and D. Presently the code takes values from ONLY from Column C and lookup in Column A. Please HELP....

Code: 
Sub ProcessLookUpValues()
  Dim X As Long, Z As Long, Index As Long
  Dim ArrLookUp As Variant, ArrIn As Variant, ArrOut As Variant, Counts As Variant
  Columns("D:G").ClearContents
  ArrLookUp = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
  ReDim Counts(1 To UBound(ArrLookUp), 1 To 1)
  ArrIn = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
  ReDim ArrOut(1 To UBound(ArrIn) + UBound(ArrLookUp), 1 To 3)
  For Z = 1 To UBound(ArrLookUp)
    For X = 1 To UBound(ArrIn)
      If UCase(ArrIn(X, 1)) Like "*" & UCase(ArrLookUp(Z, 1)) & "*" Then
        Counts(Z, 1) = Counts(Z, 1) + 1
        Index = Index + 1
        ArrOut(Index, 1) = ArrIn(X, 1)
        ArrOut(Index, 2) = ArrIn(X, 2)
        ArrOut(Index, 3) = ArrLookUp(Z, 1)
      End If
    Next
    Index = Index + 1
  Next
  Range("D1:G1") = Array("Count of Lookup Value", "Result 1", "Result 2", "Result 3 (Lookup Value")
  Range("D2:D" & 1 + UBound(ArrLookUp)) = Counts
  Range("E2:G" & UBound(ArrOut)) = ArrOut
End Sub

Sheet1[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Table Array[/TD]
[TD]Company ID[/TD]
[TD]Lookup Value[/TD]
[TD]Lookup Value 2[/TD]
[TD]Count of Lookup Value[/TD]
[TD]Result 1[/TD]
[TD]Result 2[/TD]
[TD]Result 3 (Lookup Value 1 and Lookup Value 2)[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Microsoft[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]Microsoft corp[/TD]
[TD="align: right"]12[/TD]
[TD]Microsoft[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Microsoft corp[/TD]
[TD="align: right"]12[/TD]
[TD]Coca[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]Microsoft[/TD]
[TD="align: right"]8[/TD]
[TD]Microsoft[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Raytheon Company[/TD]
[TD="align: right"]35[/TD]
[TD]FISH[/TD]
[TD]Richardson[/TD]
[TD="align: right"]2[/TD]
[TD]Microsoft Corporation[/TD]
[TD="align: right"]9[/TD]
[TD]Microsoft[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]FGH[/TD]
[TD="align: right"]10[/TD]
[TD]FGH[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]Microsoft[/TD]
[TD="align: right"]6[/TD]
[TD]Microsoft[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]Microsoft[/TD]
[TD="align: right"]8[/TD]
[TD]Raytheon[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]Microsoft.[/TD]
[TD="align: right"]3[/TD]
[TD]Microsoft[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]Microsoft Corporation[/TD]
[TD="align: right"]9[/TD]
[TD]FOLEY[/TD]
[TD]Hoag[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]Microsoft[/TD]
[TD="align: right"]6[/TD]
[TD]CARLSON[/TD]
[TD]GASKEY[/TD]
[TD="align: right"]2[/TD]
[TD]Coca[/TD]
[TD="align: right"]11[/TD]
[TD]Coca[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]Coca[/TD]
[TD="align: right"]11[/TD]
[TD]Francis[/TD]
[TD]Bozicevic[/TD]
[TD="align: right"]2[/TD]
[TD]Coca Cola[/TD]
[TD="align: right"]2[/TD]
[TD]Coca[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]Microsoft.[/TD]
[TD="align: right"]3[/TD]
[TD]Honeywell[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]Coca Cola.[/TD]
[TD="align: right"]4[/TD]
[TD]Coca[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]Coca Cola[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]Coca Cola.[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FISH & RICHARDSON P.C.[/TD]
[TD="align: right"]45[/TD]
[TD]FISH Richardson[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD]Raytheon[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Richardson Fish PC[/TD]
[TD="align: right"]75[/TD]
[TD]FISH Richardson[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD]FOLEY HOAG LLP[/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD]FISH & RICHARDSON P.C.[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FGH[/TD]
[TD="align: right"]10[/TD]
[TD]FGH[/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD]BOZICEVIC FIELD & FRANCIS[/TD]
[TD="align: right"]52[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FGH Inc[/TD]
[TD="align: right"]89[/TD]
[TD]FGH[/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD]CARLSON GASKEY & OLDS[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]18[/TH]
[TD]Foley LLP[/TD]
[TD="align: right"]57[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Raytheon Company[/TD]
[TD="align: right"]35[/TD]
[TD]Raytheon[/TD]
[/TR]
[TR]
[TH]19[/TH]
[TD]Richardson Fish PC[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Raytheon[/TD]
[TD="align: right"]25[/TD]
[TD]Raytheon[/TD]
[/TR]
[TR]
[TH]20[/TH]
[TD]Richardson PC[/TD]
[TD="align: right"]71[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]21[/TH]
[TD]Field & Francis Bozicevic LLP[/TD]
[TD="align: right"]85[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]22[/TH]
[TD]FGH Inc[/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FOLEY HOAG LLP[/TD]
[TD="align: right"]43[/TD]
[TD]FOLEY Hoag[/TD]
[/TR]
[TR]
[TH]23[/TH]
[TD]Francis Bozicevic LLP[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Foley & Hoag[/TD]
[TD="align: right"]13[/TD]
[TD]FOLEY Hoag[/TD]
[/TR]
[TR]
[TH]24[/TH]
[TD]GASKEY & OLDS CARLSON[/TD]
[TD="align: right"]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hoag and Foley[/TD]
[TD="align: right"]14[/TD]
[TD]FOLEY Hoag[/TD]
[/TR]
[TR]
[TH]25[/TH]
[TD]Foley & Hoag[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]26[/TH]
[TD]Hoag and Foley[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]27[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CARLSON GASKEY & OLDS[/TD]
[TD="align: right"]60[/TD]
[TD]CARLSON GASKEY[/TD]
[/TR]
[TR]
[TH]28[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GASKEY & OLDS CARLSON[/TD]
[TD="align: right"]92[/TD]
[TD]CARLSON GASKEY[/TD]
[/TR]
[TR]
[TH]29[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]30[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Field & Francis Bozicevic LLP[/TD]
[TD="align: right"]85[/TD]
[TD]Francis Bozicevic[/TD]
[/TR]
[TR]
[TH]31[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BOZICEVIC FIELD & FRANCIS[/TD]
[TD="align: right"]52[/TD]
[TD]Francis Bozicevic[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

V
  • Question Question
Lookup heirarchy Value in Multiple Columns and Return Value from Column
Replies
7
Views
452
Special-K99
S
A
  • Solved
vlookup formula to shows 2nd , 3rd, 4th result for same arrey value.
2
Replies
16
Views
499
Fluff
Fluff
S
  • Question Question
Excel VBA to lookup rows using multiple conditions
Replies
1
Views
483
Roncondor
R
O
  • Question Question
VBA Lookup from 2 different tables (in different sheets from a single workbook) to make logical conclusion and print result in cell
Replies
0
Views
423
ouvay
O
B
  • Solved
Using INDEX MATCH & LARGE to get value in multiple columns
Replies
3
Views
479
bunny1122
B

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top