Advanced Formula Help - INDEX MATCH MATCH? VLOOKUP? Please help :(

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
2019, 2013
Platform
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

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

CarrieWarrie

New Member
Joined
Mar 8, 2013
Messages
23
Office Version
2019, 2013
Platform
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,010
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
2019, 2013
Platform
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,010
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,095,268
Messages
5,443,446
Members
405,236
Latest member
JDru85

This Week's Hot Topics

Top