Advanced Formula Help - INDEX MATCH MATCH? VLOOKUP? Please help :(

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi guys,

Wondering if anybody can help me - I have been trying to figure out this formula for 2 days now, with no luck :(

I'm trying to figure out how to use Excel to find and return a value. It's quite difficult to explain (sorry, my brain is exhausted after trying to figure this out), so I'll try to use an example:

I have one spreadsheet tab (Inspector) that is all addresses for hotels - Postal Number, Street, Town, Postcode

I have another tab on the same spreadsheet (Sheet1) that is general addresses for every property in the area - Postal Number, Street, Town, Postcode and a unique identifier.

How on earth can I insert a function into Inspector that will look up Sheet2 based upon matching Postal Numbers and Postcode, and return the unique identifier?

I basically want the function to use the Postal Number and Postcode of row 1 in Inspector to find a row in Sheet1 that matches these two criteria, and to return the value of the unique identifier belonging to that row. I then want to apply this to every row in Inspector.

I apologise for the convoluted and poor explanation - I have tried VLOOKUP, INDEX & MATCH, INDEX MATCH MATCH, HLOOKUP... nothing seems to work :(

I've included 2 screen snips of the 2 tabs and the data (couldn't see a way of uploading the spreadsheet, apologies).

Any help at all would be amazing.

Thank you so much <3
 

Attachments

  • Spreadsheet1.JPG
    Spreadsheet1.JPG
    194.6 KB · Views: 40
  • Spreadsheet2.JPG
    Spreadsheet2.JPG
    207 KB · Views: 40

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Maybe VBA? I'm honestly not very knowledgeable on either, so whichever one is easier to understand. Thank you again <3
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
No idea what I've done wrong.
Have you checked that the columns are as shown for my yellow ones?

Have you checked the sheet names are as in my code (and as you described early in the thread)?

The file is still too big. Make up a small one that you know has some matching rows. It only needs to have 10-30 rows to show the process works then it can be employed on your large file.
To be honest, I don't want to have to have to deal with tens or hundreds of thousands of rows!
 

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

Have you checked that the columns are as shown for my yellow ones?

Have you checked the sheet names are as in my code (and as you described early in the thread)?

The file is still too big. Make up a small one that you know has some matching rows. It only needs to have 10-30 rows to show the process works then it can be employed on your large file.
To be honest, I don't want to have to have to deal with tens or hundreds of thousands of rows!

Is this any good?

 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
Is this any good?
Better, though 'Inspector' still has quite a lot of rows. Never-the-less it did let me test again and there were two small glitches in my code that could have caused it to appear to have done nothing. Try this first with the sample file you provided in post #35. carefully follow the steps from post #24 but using the code below. There should be just two Unique Address IDs returned in rows 2 & 3 of 'Inspector'.

If that works okay, then try the same thing with your big file. Be patient, it may take a while to run with very large data.

A message box should appear when it is done.

VBA Code:
Sub Unique_Address_ID_v2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, lr As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With Sheets("Sheet1")
    lr = .Range("G" & Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("Row(2:" & lr & ")"), Array(4, 7, 9))
  End With
  For i = 1 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = a(i, 3)
  Next i
  With Sheets("Inspector")
    lr = .Range("D" & Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("Row(2:" & lr & ")"), Array(8, 4))
    For i = 1 To UBound(a)
      a(i, 1) = d(a(i, 1) & "|" & a(i, 2))
    Next i
    .Range("I2:I" & lr).Value = a
  End With
  MsgBox "Done"
End Sub
 

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

Better, though 'Inspector' still has quite a lot of rows. Never-the-less it did let me test again and there were two small glitches in my code that could have caused it to appear to have done nothing. Try this first with the sample file you provided in post #35. carefully follow the steps from post #24 but using the code below. There should be just two Unique Address IDs returned in rows 2 & 3 of 'Inspector'.

If that works okay, then try the same thing with your big file. Be patient, it may take a while to run with very large data.

A message box should appear when it is done.

VBA Code:
Sub Unique_Address_ID_v2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, lr As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  With Sheets("Sheet1")
    lr = .Range("G" & Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("Row(2:" & lr & ")"), Array(4, 7, 9))
  End With
  For i = 1 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = a(i, 3)
  Next i
  With Sheets("Inspector")
    lr = .Range("D" & Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("Row(2:" & lr & ")"), Array(8, 4))
    For i = 1 To UBound(a)
      a(i, 1) = d(a(i, 1) & "|" & a(i, 2))
    Next i
    .Range("I2:I" & lr).Value = a
  End With
  MsgBox "Done"
End Sub


IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I wish I could give you the biggest hug ever!!!! You have no idea just how much I appreciate this, thank you SO MUCH!!!!!!!!!

Can I be one final pain, and ask, if it's not too much trouble, just what the VBA script is doing? I love to learn, and I have a very basic understanding of programming - it would just be the icing on the cake. If you don't have the time, not to worry, you've more than gone out of your way for me!

Thank you, thank you, THANK YOU! You are an absolute star!!!!!!!
 

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
I've had problems like this before and I think one way to do it would be to concatenate all your multiple rows into one row, therefore you have one cell to use in your lookup instead of having to do a multiple criteria lookups. You could use wildcards in your lookup as well. I think that solved my issue before
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I wish I could give you the biggest hug ever!!!! You have no idea just how much I appreciate this, thank you SO MUCH!!!!!!!!!
You're welcome, though in these times the blue part is definitely off the agenda. :eek::biggrin:


what the VBA script is doing?
First, brief comments in the code .

VBA Code:
Sub Unique_Address_ID_v2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, lr As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  
  With Sheets("Sheet1")
  'Find the last row and read columns D, G & I into an array in memory - faster to process there than on the worksheet
    lr = Range("G" & Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("Row(2:" & lr & ")"), Array(4, 7, 9))
  End With
  
  'Put all the relvant data into a dictionary - see separate notes about this
  For i = 1 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = a(i, 3)
  Next i
  
  
  With Sheets("Inspector")
    'Find the last row and read columns H & D (in that order) into an array - again for speed
    lr = Range("D" & Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("Row(2:" & lr & ")"), Array(8, 4))
    
    'Go through the array and get the results back out of the dictionary - again see other notes
    For i = 1 To UBound(a)
      a(i, 1) = d(a(i, 1) & "|" & a(i, 2))
    Next i
    
    'Write the results into column I
    .Range("I2:I" & lr).Value = a
  End With
End Sub


Now, in relation to the dictionary, consider this small sample of Sheet1

CarrieWarrie TEST3.xlsm
DGI
1Building numberPostcodeUnique Address ID
222-24BT54 6LX185842797
361BT20 3TA185199318
419-21BT1 2AA185000560
Sheet1


The dictionary object is much like a language dictionary which consists of a list of words and a corresponding meaning of each word.
In the code these entries are made into the dictionary

WordMeaning
22-24|BT54 6LX185842797
61|BT20 3TA185199318
19-21|BT1 2AA185000560



Now let's look at this small sample of 'Inspector'

CarrieWarrie TEST3.xlsm
DHI
1PostCodePostal NumberUnique Address ID
2BT54 6LX22-24
3BT31 9DW28
4BT20 3TA61
Inspector


Columns H & D are read into an array so we have

22-24BT54 6LX
28BT31 9DW
61BT20 3TA


Join the first 2 entries as "22-24|BT54 6LX", look that entry up in the dictionary, bring back the meaning (185842797) & put it in column 1 (row 1) of the array
Second one "28|BT31 9DW" look that up in the dictionary & bring back what is found (nothing or "") & put it in column 1 (row 2) of the array
Third "61|BT20 3TA" look that up and bring back what is found (185199318) & put it in column 1 (row 3) of the array.

So the array now looks like this

185842797BT54 6LX
BT31 9DW
185199318BT20 3TA


Now write column 1 of the array into column I of 'Inspector'

CarrieWarrie TEST3.xlsm
DHI
1PostCodePostal NumberUnique Address ID
2BT54 6LX22-24185842797
3BT31 9DW28
4BT20 3TA61185199318
Inspector
 

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
You're welcome, though in these times the blue part is definitely off the agenda. :eek::biggrin:


First, brief comments in the code .

VBA Code:
Sub Unique_Address_ID_v2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, lr As Long
 
  Set d = CreateObject("Scripting.Dictionary")
 
  With Sheets("Sheet1")
  'Find the last row and read columns D, G & I into an array in memory - faster to process there than on the worksheet
    lr = Range("G" & Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("Row(2:" & lr & ")"), Array(4, 7, 9))
  End With
 
  'Put all the relvant data into a dictionary - see separate notes about this
  For i = 1 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = a(i, 3)
  Next i
 
 
  With Sheets("Inspector")
    'Find the last row and read columns H & D (in that order) into an array - again for speed
    lr = Range("D" & Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("Row(2:" & lr & ")"), Array(8, 4))
   
    'Go through the array and get the results back out of the dictionary - again see other notes
    For i = 1 To UBound(a)
      a(i, 1) = d(a(i, 1) & "|" & a(i, 2))
    Next i
   
    'Write the results into column I
    .Range("I2:I" & lr).Value = a
  End With
End Sub


Now, in relation to the dictionary, consider this small sample of Sheet1

CarrieWarrie TEST3.xlsm
DGI
1Building numberPostcodeUnique Address ID
222-24BT54 6LX185842797
361BT20 3TA185199318
419-21BT1 2AA185000560
Sheet1


The dictionary object is much like a language dictionary which consists of a list of words and a corresponding meaning of each word.
In the code these entries are made into the dictionary

WordMeaning
22-24|BT54 6LX185842797
61|BT20 3TA185199318
19-21|BT1 2AA185000560



Now let's look at this small sample of 'Inspector'

CarrieWarrie TEST3.xlsm
DHI
1PostCodePostal NumberUnique Address ID
2BT54 6LX22-24
3BT31 9DW28
4BT20 3TA61
Inspector


Columns H & D are read into an array so we have

22-24BT54 6LX
28BT31 9DW
61BT20 3TA


Join the first 2 entries as "22-24|BT54 6LX", look that entry up in the dictionary, bring back the meaning (185842797) & put it in column 1 (row 1) of the array
Second one "28|BT31 9DW" look that up in the dictionary & bring back what is found (nothing or "") & put it in column 1 (row 2) of the array
Third "61|BT20 3TA" look that up and bring back what is found (185199318) & put it in column 1 (row 3) of the array.

So the array now looks like this

185842797BT54 6LX
BT31 9DW
185199318BT20 3TA


Now write column 1 of the array into column I of 'Inspector'

CarrieWarrie TEST3.xlsm
DHI
1PostCodePostal NumberUnique Address ID
2BT54 6LX22-24185842797
3BT31 9DW28
4BT20 3TA61185199318
Inspector


You are an absolute gentleman - thank you so much for your amazing help, patience and for the time you've taken to help me! Words can't express how grateful I am! You are an Excel genius!

Wishing you and your family good health in the middle of the madness that we're all facing - thank you again!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,276
Messages
5,641,273
Members
417,202
Latest member
AndyVBA

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
Top