#### CarrieWarrie

##### New Member
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

194.6 KB · Views: 40
207 KB · Views: 40

#### sandy666

##### Banned - Rules violations
I don't mind, whatever is easier for you to use
it's up to you not me

### 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
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
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

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
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

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
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
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 .

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

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
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

 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
DHI
2BT54 6LX22-24
3BT31 9DW28
4BT20 3TA61
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
DHI
2BT54 6LX22-24185842797
3BT31 9DW28
4BT20 3TA61185199318
Inspector

#### CarrieWarrie

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

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

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
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

 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
DHI
2BT54 6LX22-24
3BT31 9DW28
4BT20 3TA61
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
DHI
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!

Replies
2
Views
460
Replies
21
Views
378
Replies
3
Views
126
Replies
1
Views
186
Replies
1
Views
146

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.

### Which adblocker are you using?

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

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