Search and Match across multiple columns

[Liquid_Len]

Hi all,

I hope I have posted this correctly, it is my first post on this site and welcome any feedback.

I have a problem that I have been working on for a few days now, but I keep coming up with brick walls. I have some data that populates a worksheet via entry of a MS Forms. The data is required to be entered in such a way that the data comes in the following format (this cannot be changed for operational reasons)

I can use INDEX and MATCH to find Code 1 based on the data in Name 1 & Course 1 and return this into a cell on another sheet. However, what I need to do is search across all of the columns. For example, if I enter Katy and MNO234 in the appropriate cells, CODE567 is returned in the adjacent cell. To make matters worse, there are other columns of data between the columns demonstrated above.

I'm hoping that there is a simple solution using a Formula, but I have some (limited) VBA experience so happy to go down that route.

Thanks in advance.

Len

 

[James006]

Hi,

How many blocks of "Name-Course-Code" do you store in Row 1 ?

You could make your life so much easier with a clean properly structured Database ...

 

[Liquid_Len]

Hi,

How many blocks of "Name-Course-Code" do you store in Row 1 ?

You could make your life so much easier with a clean properly structured Database ...

Hi James,

Thank you for the prompt reply. The structure is as in the screen shot above, ie 3 blocks of Name-Course-Code. Agreed about the database, but our organisaton do not like staff creating databases (they don't even allow us to have Access installed on our latops). Also, the spreadsheet has to be accessed by other members of staff who are not very technically minded and it's a struggle to get them to use Excel let alone anything else!

Thanks,

Len

 

[James006]

Understand your constraint ...

Are you free to add a Database Sheet ... designed by you in a smart way for your current (and future) requirements ...???

 

[Tupe77]

How do you know which result you got (code1,code 2 or code 3)? Or what is the purpose of the numbers in the titles?

 

[Liquid_Len]

How do you know which result you got (code1,code 2 or code 3)? Or what is the purpose of the numbers in the titles?

As per above, if you type Katy, MNO234 it will return the adjacent cell which in this case is CODE567

Thanks,

Len

 

[Tupe77]

I am chasing the purpose of numbering the titles.

Why aren't only the titles: Name, Course and Code used?

 

[Peter_SSs]

Welcome to the MrExcel board!

To make matters worse, there are other columns of data between the columns demonstrated above.

Probably better to give us a true idea of the real situation then rather than putting time into developing a solution for what you have given us and then having to (possibly) completely rethink it when the real situation is revealed.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Liquid_Len]

I am chasing the purpose of numbering the titles.

Why aren't only the titles: Name, Course and Code used?

Hi,

The data comes in from a MS Form (which others have access to change) and requires the collection of multiple users and the courses they have taken, hence the Name1, Course1, Code1, etc.. The format of this could change over time.

It will probably be best to do what Peter has suggested and strip out all the sensitive data / vba and upload it to the forum so that it can be worked on.

 

[Peter_SSs]

It will probably be best to do what Peter has suggested and strip out all the sensitive data / vba and upload it to the forum so that it can be worked on.

We only need a smallish sample not your whole data - like your previous sample should do but with the relevant number of additional columns.
I'm also guessing that name can occur more than once (with a different course/code) so if that is so, include one or two of those as well.
Also, best not to completely remove the sensitive data and leave those cells blank, just replace the sensitive data with dummy data.