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

outcome to appear the same as Table1 with just the UPRN field populated
maybe it will work for you

btw. what is it UPRN ???

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### sandy666

##### Banned - Rules violations
would be good to see example with some matching data not with empty result

#### CarrieWarrie

##### New Member
try again

maybe it will work for you

btw. what is it UPRN ???
Oh, I'm so stupid - UPRN is just the Unique Address Identifier, sorry!

I tried that again, it pulls back the unique address ID, but they are incorrect

I have the document on OneDrive, link below:

#### Peter_SSs

##### MrExcel MVP, Moderator
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.
To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code I provided into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Note also that my code does depend on the columns that I highlighted being in those particular columns on your real sheets. If not some adjustment to the code would be needed.

#### CarrieWarrie

##### New Member

would be good to see example with some matching data not with empty result

Updated all, as per your suggestions

Honestly, I can't thank you enough for all of this!!!!!

#### CarrieWarrie

##### New Member
To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code I provided into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Note also that my code does depend on the columns that I highlighted being in those particular columns on your real sheets. If not some adjustment to the code would be needed.

I am honestly so embarrassed about how little I know - but I appreciate your patience with me. I have uploaded my actual document onto OneDrive, does this help any?

#### Peter_SSs

##### MrExcel MVP, Moderator

I have the document on OneDrive, link below:
Link does not work for me. File exceeds size limit. We really need small sample files that are sufficient to demonstrate the issue(s) involved.

Thank you for updating your profile.

Have you tried implementing the macro as described?

#### sandy666

##### Banned - Rules violations
I have the document on OneDrive, link below:
zip this file and post link again (to zip file), onedrive tried to open this file but it doesn't work with bigger than 5 mb

btw. do you want Power Query or VBA ?

#### CarrieWarrie

##### New Member
Link does not work for me. File exceeds size limit. We really need small sample files that are sufficient to demonstrate the issue(s) involved.

Thank you for updating your profile.

Have you tried implementing the macro as described?

I have, but nothing happens No idea what I've done wrong.

I have zipped the document, but I think it may still be too large

#### CarrieWarrie

##### New Member
zip this file and post link again (to zip file), onedrive tried to open this file but it doesn't work with bigger than 5 mb

btw. do you want Power Query or VBA ?

I've zipped the document, but I think it may still be too large

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

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

1,130,275
Messages
5,641,246
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