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

[CarrieWarrie]

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

 

[sandy666]

I don't mind, whatever is easier for you to use

it's up to you not me

 

[CarrieWarrie]

Maybe VBA? I'm honestly not very knowledgeable on either, so whichever one is easier to understand. Thank you again <3

 

[sandy666]

Maybe VBA?

sure, I leave you in Peter's hands

 

[Peter_SSs]

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]

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?

TEST3.xlsx

1drv.ms

 

[Peter_SSs]

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.

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]

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.

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]

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]

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.

what the VBA script is doing?

First, brief comments in the 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
	D			G		I
1	Building number			Postcode		Unique Address ID
2	22-24			BT54 6LX		185842797
3	61			BT20 3TA		185199318
4	19-21			BT1 2AA		185000560
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

Word	Meaning
22-24|BT54 6LX	185842797
61|BT20 3TA	185199318
19-21|BT1 2AA	185000560

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

CarrieWarrie TEST3.xlsm
	D				H	I
1	PostCode				Postal Number	Unique Address ID
2	BT54 6LX				22-24
3	BT31 9DW				28
4	BT20 3TA				61
Inspector

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

22-24	BT54 6LX
28	BT31 9DW
61	BT20 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

185842797	BT54 6LX
	BT31 9DW
185199318	BT20 3TA

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

CarrieWarrie TEST3.xlsm
	D				H	I
1	PostCode				Postal Number	Unique Address ID
2	BT54 6LX				22-24	185842797
3	BT31 9DW				28
4	BT20 3TA				61	185199318
Inspector

 

[CarrieWarrie]

You're welcome, though in these times the blue part is definitely off the agenda.


First, brief comments in the 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
	D			G		I
1	Building number			Postcode		Unique Address ID
2	22-24			BT54 6LX		185842797
3	61			BT20 3TA		185199318
4	19-21			BT1 2AA		185000560
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

Word	Meaning
22-24|BT54 6LX	185842797
61|BT20 3TA	185199318
19-21|BT1 2AA	185000560

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

CarrieWarrie TEST3.xlsm
	D				H	I
1	PostCode				Postal Number	Unique Address ID
2	BT54 6LX				22-24
3	BT31 9DW				28
4	BT20 3TA				61
Inspector

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

22-24	BT54 6LX
28	BT31 9DW
61	BT20 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

185842797	BT54 6LX
	BT31 9DW
185199318	BT20 3TA

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

CarrieWarrie TEST3.xlsm
	D				H	I
1	PostCode				Postal Number	Unique Address ID
2	BT54 6LX				22-24	185842797
3	BT31 9DW				28
4	BT20 3TA				61	185199318
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!