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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
I realise how rubbish my explanation is, sorry guys -

I really want a formula that will mean that I don't have to go through each row manually looking up the postal number and postcode to find the matching unique property reference number in Sheet1.

I know what I want the formula to do, I just lack the knowledge of how to code it. I'd like it to take the postal number and the postcode for one record in Inspector, go through each row in Sheet1 and find a record that matches the postal number and postcode, then return the unique identifier for that record.

Hopefully this explains it a little better.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

You are welcome

btw. imho, you can post a link to the shared excel file with your source data and expected result, using onedrive, googledrive or any similar. will be much easier than with pictures for anyone who will try to help you
 

CarrieWarrie

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

btw. imho, you can post a link to the shared excel file with your source data and expected result, using onedrive, googledrive or any similar. will be much easier than with pictures for anyone who will try to help you

Done :) Couldn't see an option to edit my original post, but the link is in my comment at the bottom. Thank you for all of your suggestions!
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
sure, I forgot to say before but would be fine if you update your profile about Excel version and OS - just click on your screenname at the right top of the forum then Account Details
you can edit your own post in 10 minutes, after that there is no option to edit except call moderator.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
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