#### 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
based upon matching Postal Numbers and Postcode, and return the unique identifier

these two tables do not contain common Postal Number and Postcode so the result does not have a Unique Address ID

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### CarrieWarrie

##### New Member
these two tables do not contain common Postal Number and Postcode so the result does not have a Unique Address ID
The sample data won't, but the actual data is over a milllion records, so it's in there somewhere This is an absolute nightmare - I'm thinking I'm going to have to do all of this manually :'(

#### sandy666

##### Banned - Rules violations
with your example I can find UAID by Postal Number only as you can see

but the rest is null

#### CarrieWarrie

##### New Member
with your example I can find UAID by Postal Number only as you can see
View attachment 10533
but the rest is null

I know, apologies - that's only the first couple of examples - the actual dataset is well over a million records, so there will be matches based upon postal number and psotcode. If I could just figure out how to do it I wish I was better with Excel

#### sandy666

##### Banned - Rules violations

I can give you example where you can paste data into Table1 and Table2 without changing structure (no more or less columns) then right click on third table (green) and select refresh.
Maybe it will work for you. Is that ok?

#### CarrieWarrie

##### New Member
I can give you example where you can paste data into Table1 and Table2 without changing structure (no more or less columns) then right click on third table (green) and select refresh.
Maybe it will work for you. Is that ok?

That would be AMAZING!!!! Thank you so, so, so much <3

#### sandy666

##### Banned - Rules violations

btw. if there will be a problem with read-only just right click on the not open file and choose Properties and un-check read-only
this is the simplest version

#### Peter_SSs

##### MrExcel MVP, Moderator
The sample data won't,
It really would have been better to make a very small sample of dummy data that did at least have some results.
Also,
- you will get more helpers if you post your samples directly in the forum using XL2BB
- results can be better tailored to your circumstances if you do what sandy666 suggested:
.. update your profile about Excel version and OS - just click on your screenname at the right top of the forum then Account Details ..

If you are interested in a macro approach here is one to consider.
VBA Code:
``````Sub Unique_Address_ID()
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
End Sub``````

Here is my sample data and results. I have modified some of the data to ensure at least a few results.

CarrieWarrie.xlsx
DEFGHI
220DONEGALL QUAYBELFASTBT99 1EA20 DONEGALL QUAY, BELFAST, BT99 1EA185000000
319ROYAL AVENUEBELFASTBT1 1FB19 ROYAL AVENUE, BELFAST, BT1 1FB185000115
441ROYAL AVENUEBELFASTBT1 1FD41 ROYAL AVENUE, BELFAST, BT1 1FD185000117
5EstateROYAL AVENUEBELFASTBT49 0HP41-51 ROYAL AVENUE, BELFAST, BT1 1FD185000118
645-47ROYAL AVENUEBELFASTBT1 1FD45-47 ROYAL AVENUE, BELFAST, BT1 1FD185000119
749-51ROYAL AVENUEBELFASTBT21 0HE49-51 ROYAL AVENUE, BELFAST, BT1 1FD185000122
833-39ROYAL AVENUEBELFASTBT1 1FD33-39 ROYAL AVENUE, BELFAST, BT1 1FD185000123
973ROYAL AVENUEBELFASTBT1 1FE73 ROYAL AVENUE, BELFAST, BT1 1FE185000125
1085-87ROYAL AVENUEBELFASTBT1 1FE85-87 ROYAL AVENUE, BELFAST, BT1 1FE185000127
11109ROYAL AVENUEBELFASTBT33 0LN91 ROYAL AVENUE, BELFAST, BT1 1FE185000128
1271ROYAL AVENUEBELFASTBT1 1FE71 ROYAL AVENUE, BELFAST, BT1 1FE185000129
1371ROYAL AVENUEBELFASTBT1 1FE71 ROYAL AVENUE, BELFAST, BT1 1FE185000130
Sheet1

CarrieWarrie.xlsx
DEFGHI
2BT54 6LXAntrimGuest House1422-24
4BT31 9DWDownGuest House828
5BT99 1EAAntrimGuest House720185000000
6BT29 4HGAntrimGuest House8102
7BT30 6ABDownGuest House614-16
8BT57 8SNAntrimGuest House12306
9BT33 0LNDownGuest House12109185000128
10BT57 8SUAntrimGuest House1123
11BT47 4AALondonderryGuest House311
12BT44 0QZAntrimGuest House710
13BT93 2BBFermanaghGuest House333
14BT49 0HPLondonderryGuest House12Estate185000118
15BT94 5HGFermanaghGuest House13Belle
16BT28 2HQAntrimGuest House1113
17BT30 8HZDownGuest House153
18BT67 0QLArmaghGuest House658
19BT21 0HEDownGuest House649-51185000122
21BT71 7SGTyroneGuest House1716
23BT20 3BHDownGuest House1245
24
Inspector

#### CarrieWarrie

##### New Member
btw. if there will be a problem with read-only just right click on the not open file and choose Properties and un-check read-only
this is the simplest version
YOU GENIUS! This absolutely works - thank you sooooooo much! The only issue I'm having now, is that the results table are all out of order - I was hoping for the outcome to appear the same as Table1 with just the UPRN field populated, is that just not possible?

#### CarrieWarrie

##### New Member
It really would have been better to make a very small sample of dummy data that did at least have some results.
Also,
- you will get more helpers if you post your samples directly in the forum using XL2BB
- results can be better tailored to your circumstances if you do what sandy666 suggested:
Hi Peter! Thank you for your reply, I really appreciate it! I know, I really am rubbish at this

I love the macro idea, but I really am a novice, so I have no idea how to insert this code into my spreadsheet at all.

Last edited by a moderator:

Replies
2
Views
452
Replies
21
Views
353
Replies
3
Views
111
Replies
1
Views
182
Replies
1
Views
131

1,129,800
Messages
5,638,433
Members
417,025
Latest member
MusterDuster

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