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]

try again

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

Microsoft Excel Workbook

1drv.ms

outcome to appear the same as Table1 with just the UPRN field populated

maybe it will work for you

btw. what is it UPRN ???

 

[sandy666]

would be good to see example with some matching data not with empty result
and again: update your profile about excel version and OS (post#10)

 

[CarrieWarrie]

try again

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

Microsoft Excel Workbook

1drv.ms

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:

TEST2.xlsx

Microsoft Excel Workbook

1drv.ms

 

[Peter_SSs]

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]

would be good to see example with some matching data not with empty result
and again: update your profile about excel version and OS (post#10)

Updated all, as per your suggestions

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

 

[CarrieWarrie]

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?

TEST2.xlsx

Microsoft Excel Workbook

1drv.ms

 

[Peter_SSs]

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]

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]

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

Microsoft OneDrive

1drv.ms

 

[CarrieWarrie]

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

Microsoft OneDrive

1drv.ms

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