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

 

[CarrieWarrie]

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]

use Power Query with merge feature

 

[CarrieWarrie]

use Power Query with merge feature

Hi Sandy, thank you for your reply - I don't have a clue what Power Query is

 

[sandy666]

so now it will be hard for you but for future
more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[CarrieWarrie]

so now it will be hard for you but for future
more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

Thank you

 

[sandy666]

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]

As suggested by sandy666, here is a link to my document in OneDrive:

Hotel Spreadsheet

 

[CarrieWarrie]

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]

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.