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

Excel Facts

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

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. (y)

Have you tried implementing the macro as described?
 

sandy666

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

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

Watch MrExcel Video

Forum statistics

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