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]

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

 

[CarrieWarrie]

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]

with your example I can find UAID by Postal Number only as you can see

but the rest is null

 

[CarrieWarrie]

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]

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]

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]

so just download (not edit) and play with your all data

s666-PQ--Hotel - Unique Number Match.xlsx

Microsoft Excel Workbook

1drv.ms

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]

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.

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
	D	E	F	G	H	I
1	Postal Number	Primary Thoroughfare	Town	Postcode	Address String	Unique Address ID
2	20	DONEGALL QUAY	BELFAST	BT99 1EA	20 DONEGALL QUAY, BELFAST, BT99 1EA	185000000
3	19	ROYAL AVENUE	BELFAST	BT1 1FB	19 ROYAL AVENUE, BELFAST, BT1 1FB	185000115
4	41	ROYAL AVENUE	BELFAST	BT1 1FD	41 ROYAL AVENUE, BELFAST, BT1 1FD	185000117
5	Estate	ROYAL AVENUE	BELFAST	BT49 0HP	41-51 ROYAL AVENUE, BELFAST, BT1 1FD	185000118
6	45-47	ROYAL AVENUE	BELFAST	BT1 1FD	45-47 ROYAL AVENUE, BELFAST, BT1 1FD	185000119
7	49-51	ROYAL AVENUE	BELFAST	BT21 0HE	49-51 ROYAL AVENUE, BELFAST, BT1 1FD	185000122
8	33-39	ROYAL AVENUE	BELFAST	BT1 1FD	33-39 ROYAL AVENUE, BELFAST, BT1 1FD	185000123
9	73	ROYAL AVENUE	BELFAST	BT1 1FE	73 ROYAL AVENUE, BELFAST, BT1 1FE	185000125
10	85-87	ROYAL AVENUE	BELFAST	BT1 1FE	85-87 ROYAL AVENUE, BELFAST, BT1 1FE	185000127
11	109	ROYAL AVENUE	BELFAST	BT33 0LN	91 ROYAL AVENUE, BELFAST, BT1 1FE	185000128
12	71	ROYAL AVENUE	BELFAST	BT1 1FE	71 ROYAL AVENUE, BELFAST, BT1 1FE	185000129
13	71	ROYAL AVENUE	BELFAST	BT1 1FE	71 ROYAL AVENUE, BELFAST, BT1 1FE	185000130
Sheet1

CarrieWarrie.xlsx
	D	E	F	G	H	I
1	PostCode	County	Category	Rooms/Units	Postal Number	Unique Address ID
2	BT54 6LX	Antrim	Guest House	14	22-24
3	BT20 3TA	Down	Guest House	13	61
4	BT31 9DW	Down	Guest House	8	28
5	BT99 1EA	Antrim	Guest House	7	20	185000000
6	BT29 4HG	Antrim	Guest House	8	102
7	BT30 6AB	Down	Guest House	6	14-16
8	BT57 8SN	Antrim	Guest House	12	306
9	BT33 0LN	Down	Guest House	12	109	185000128
10	BT57 8SU	Antrim	Guest House	11	23
11	BT47 4AA	Londonderry	Guest House	3	11
12	BT44 0QZ	Antrim	Guest House	7	10
13	BT93 2BB	Fermanagh	Guest House	3	33
14	BT49 0HP	Londonderry	Guest House	12	Estate	185000118
15	BT94 5HG	Fermanagh	Guest House	13	Belle
16	BT28 2HQ	Antrim	Guest House	11	13
17	BT30 8HZ	Down	Guest House	15	3
18	BT67 0QL	Armagh	Guest House	6	58
19	BT21 0HE	Down	Guest House	6	49-51	185000122
20	BT20 3TA	Down	Guest House	10	51
21	BT71 7SG	Tyrone	Guest House	17	16
22	BT34 3AD	Down	Guest House	6	49
23	BT20 3BH	Down	Guest House	12	45
24
Inspector

 

[CarrieWarrie]

so just download (not edit) and play with your all data

s666-PQ--Hotel - Unique Number Match.xlsx

Microsoft Excel Workbook

1drv.ms

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]

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.