Multiple column search

[Chris2208]

Hi All,

Thanks for any help that comes my way.

A bit of background on the problem to hopefully provide context. I have over 200,000 records of wildlife collected from over 200 sites over the last few years. Unfortunately the person putting the cameras out called many of the sites different names at different times. This means I have a list of over 200,000 records where I am not sure if the site code is right. I have translated all the site code data so I now know what site was what but now I need to link this table with the table with all the records.

What I have.
Table 1 has the correct site code in the far left column and then another 5 columns with the incorrect site codes in them (yes some sites were called four different things)
Table 2 has over 200,000 records in it with the far right column being the site code (the unadulterated and sometimes wrong site code.

What I want to do is add the right site code to table 2. I have tried vlookup and nested if functions but I can't get anything to lookup the multiple columns in table 1 to see if there is a match in one column and then return the far left value for the corresponding row.

In essence I need a formula that will match the site code in table 2 with the site code in one of the columns in table 2 and return the right site code back to me.

Thanks for any assistance.
Chris

[PFDave]

Hi Chris,

You can use multiple Index and Match for this. May be a leaner way but this will do the job. Where I have placed M1 This is an assumption for your far right column being the site code. Please amend as appropriate.

You will need to consider the location of the table (e.g. sheet name) and apply this in, or you can advise and I can help with that. As I don't the know the headers for your tables I cannot write in that way.

Hopefully this will help you on the right solution:

Please Login or Register  to view this content.

[Chris2208]

Thanks Dave.

I had to change the IFNA to IFERROR as 2010 doesn't know IFNA and it kept giving me a #Name error. Other than that it worked well.

Thanks for your help.
Chris