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]

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

Are you free to add a worksheet to your workbook ... and design your Database... in your own smart way, for your current (and future) requirements ...???

 

[Liquid_Len]

Hi all,

Just to give a quick update to my problem, I managed to solve the issue myself. It seems I couldn't see the wood for the trees and was trying to find a more difficult solution to the problem. James006 gave me the idea after I came back to revisit the issue.

I solved it by simply copying the separate columns Name (1,2,3) & Code (1,2,3) into 2 columns (Name & Code) on an analysis sheet. After that it was a simple case of running a Vlookup on these columns and returning the value into the main sheet.

Thanks for all your input.

Len