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

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
7,499
with your example I can find UAID by Postal Number only as you can see
postalnumber.png

but the rest is null
 

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

so just download (not edit) and play with your all data
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 :biggrin:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
The sample data won't,
Giving sample data that produces no results is not very helpful. ;)
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
1Postal NumberPrimary ThoroughfareTownPostcodeAddress StringUnique Address ID
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
1PostCodeCountyCategoryRooms/UnitsPostal NumberUnique Address ID
2BT54 6LXAntrimGuest House1422-24
3BT20 3TADownGuest House1361
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
20BT20 3TADownGuest House1051
21BT71 7SGTyroneGuest House1716
22BT34 3ADDownGuest House649
23BT20 3BHDownGuest House1245
24
Inspector
 

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
so just download (not edit) and play with your all data
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 :biggrin:
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
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Giving sample data that produces no results is not very helpful. ;)
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:

Watch MrExcel Video

Forum statistics

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