Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Lookup in multiple columns

0

Hello experts

I am new to this forum and I hope to find answer to my question

In a sheet in column A I have some names

In another sheet there are several columns : the first column would be IDs and the columns D to H has names too 

What I was after is to search the names of the first sheet in the columns D to H in the second sheet then return the IDs of the name of found in any columns

Answer
Discuss

Discussion

In which column is the ID?
Variatus (rep: 4889) Apr 23, '18 at 8:10 pm
Thanks a lot
the first column would be IDs (column A) in Sheet2
NerdyHerdy (rep: 4) Apr 24, '18 at 8:09 am
Add to Discussion

Answers

0
Selected Answer

Here is the formula which will do the job.It will look for a match of the name in A2 in the range called "Names" and return the number it finds in that row of Sheet("Numbers") in column A.

=INDEX(Numbers!$A:$A,SUMPRODUCT((Names=$A2)*ROW(Names)),1)

Before it can be put to work some preparation is required.

  1. Name the second sheet in your workbook as "Numbers".
    Better still, give it a more meaningful name and replace the word in my formula. Remember, if your name contains spaces you must enclose it in apostrophs in the formula, like INDEX('My Numbers'!$A:$A
  2. On that same sheet, select the range containing the names and write the name you prefer in the Names Bar (to the left of the Formula Bar, at the top of the sheet). Then replace "Names" in the formula with the name you gave to that range.
    In my test the range was Numbers!D2:H9. In your workbook it is likely to be much larger. You can replace "Names" in the formula with"Numbers!$D$2:$H$9" (or your own range address) but giving it a name is neater. The formula is easier to read and when you change the range in future you call up the Name Manager (Formulas tab of the ribbon) and amend it in one place instead of having to change hundreds of formulas.
Discuss

Discussion

That's perfect and exactly as what I was searching for.
Thanks a lot for great help and perfect explanation 
NerdyHerdy (rep: 4) Apr 25, '18 at 12:52 am
Add to Discussion
0

Hello NerdyHerdy,

Sorry, I'm just a learner and don't know how far I understood your query.

Sewing some googled patches together, I've prepared a file that needs a lot of tidyings. I hope, experts here will also look into it.

Looking forward for your feedback...

I couldn't upload the file in the forum, so please click here to download.

Discuss

Discussion

Thanks a lot Chhabi Acharya for your great file. I appreciate a lot your help
NerdyHerdy (rep: 4) Apr 25, '18 at 12:58 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login