lookup return multiple value

Microsoft Book: office excel 2003 inside...
August 21, 2009 at 03:41:11
Specs: Windows XP
I have following list in sheet2:

Column A--Column B-------Column C----------------------Column D

Sl.No.--Employee name--designation with pay-------place of posting

1----------Jack-------------Executive Engineer--------------Leopol
-----------------------------12800 per month---------------------
2---------Bill----------------Junior Engineer------------------Totem
-------------------------------6000 permonth-----------------------
--------------------------------92 per square feet------------------

so on...

Now i just want to type Jack in sheet1 A1 and get Jack details as:

in B1=Executive Engineer
in B2=12800 per month
in C1=Leopol

See More: lookup return multiple value

Report •

August 21, 2009 at 17:03:43
Have you looked at VLOOKUP in the Excel Help files?

The syntax is:


By using a different col_index_num in each VLOOKUP formula, you'll pull different data from the table_array.

Report •

August 21, 2009 at 22:22:04
I tried with it and also with index, match function. But it does not show up "pay" i.e. 12800 per month which is in the next row below executive Engineer. I feel a macro/VBA would do the best but i don't know how to write it. I have seen one at below website which is near to what i need but here i have to copy down formula. Need your help.


Report •

August 22, 2009 at 11:05:42
So I guess your data (formatted using the pre tags) looks like this...

    A	     B	                C	              D
1  Sl.No  Employee name  designation with pay    place of posting
2   1	Jack	        Executive Engineer      Leopol
3		        12800 per month	
4   2	Bill	        Junior Engineer	     Totem
6		        6000 per month	
7   3	John	        Painter                 Salem
8		        92 per square foot

I used this to find salary for a name entered in A10:


Adjust to work across sheets and you should be set.

Report •

Related Solutions

August 23, 2009 at 22:54:45
You guess my data format correctly. I typed Jack in A10 and copied your formula to Cell B10 as wel as in B11 but gives #N/A error.

Isn't it possible to write VBA to look employee name from column B and return that particular employee designation and place of posting plus data in immediate row below i.e salary row?.

For example, when i type Jack in A1 or Bill in A10 of sheet1 it should return(automate) following data from sheet2 which contains data format as you guessed:

-------Column A-----Column B----------Column C--
Row1----Jack----Executive Engineer------Leopol
Row2-------------12800 per month-----------

Row10---Bill------Junior Engineer---------Totem
Row11--------------6000 per month---------

Report •

August 24, 2009 at 07:21:40
Yes, it's possible to write VBA code to do what you want, but it's not needed. Using VBA code to do what a formula will do just adds more complexity to the workbook, including issues with sending the workbook to others, code maintenance, etc.

To find the Employee Designation and Place of Posting, a standard VLOOKUP will work. To find the salary, the OFFSET/MATCH/VLOOKUP combination gets the job done.

If I "guessed" at your data layout correctly, and the formula works as written on the 2 machines I've tried it on, including using the exact cells you posted in your response, then the problem is on your end, not with the formula construction. Did you modify the formula so that it is looking at the table in Sheet2?

I suggest you use Tools...Formula Auditing...Evaluate Formula to find out why you are getting a #N/A error.

FYI...to adjust the formula to read data from the table in Sheet2, the formula would look like this, where Sheet2 is the name of the sheet where your table exists:


I did this and it works across the 2 sheets.

If you insist on using a macro then this seemed to work for me. Right click the sheet tab for Sheet1, choose view code and paste this into the window that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 1 And Target.Cells.Count = 1 Then
  Application.EnableEvents = False
   With Sheets(2).Range("B1:B" & Rows.Count)
    Set c = .Find(Target, LookIn:=xlValues, lookat:=xlWhole)
     If Not c Is Nothing Then
        Target.Offset(0, 1) = c.Offset(0, 1)
        Target.Offset(0, 2) = c.Offset(0, 2)
        Target.Offset(1, 1) = c.Offset(1, 1)
     End If
      If c Is Nothing Then MsgBox Target & " Not Found"
   End With
 End If
  Application.EnableEvents = True
End Sub

Report •

August 25, 2009 at 21:09:48
Thanks you so much Derby. Your code works perfect. Yes formula too works now. It seems that Sheet1!A10 instead of A10 would be the culprit and i don't know why it should be.

Report •

August 26, 2009 at 06:49:08
re: Sheet1!A10

Was the formula placed in Sheet1? If it was, then you shouldn't need Sheet1!A10. If not, then of course you would.

Report •

August 26, 2009 at 21:11:05
Yes the formula was placed in sheet1. One more problem come up, the data is not updating automatically from the source sheet (while using code). sorry that i did not mention this earlier( I thought that there will be no such problem). will you help me further?.

Report •

August 28, 2009 at 01:15:13
I also tried the above code for my worksheet with some modification with same problem but got this workaround, Select the cell you want to update. press F2 then hit enter.

Report •

August 28, 2009 at 03:37:17
But i have 500 plus employee names. oh! that will require me to hit more than 1500 times(select+F2+Enter x 500)

Report •

August 28, 2009 at 05:20:12
I don't know what you mean by this :

the data is not updating automatically from the source sheet

Report •

August 28, 2009 at 22:50:09
My master data is in sheet2 then i used the above code in sheet1. it is working perfect except that it does not auto updating its data in sheet1 whenever i made changes in Sheet2.

For example, when i typed Jack in sheet1, it pick up Jack's data from sheet2 but when i change Jack's place of posting at later date from leopol (see response No 3) to Japan in sheet2, Jack place of posting still remain leopol in sheet1. I want Japan to comeup automatically in sheet1.

Derby, You are my last hope.

Report •

August 29, 2009 at 11:40:49
I strongly suggested that you use the formulae offered instead of the code for a number of reasons.

The main one being that you do not understand what the code is doing and therefore cannot maintain it if changes are needed. If you understood what the code was doing, you'd understand why you have make a change to Sheet1 (F2) in order to have the changes to Sheet2 show up in Sheet1.

You kept asking for code to pull data from Sheet2 when a name was entered in Sheet1 and that is what I provided. You didn't ask that the code also update Sheet1 if a change is made to Sheet2.

If you want the data in Sheet1 to automatically update when changes are made to Sheet2, then use the formulae instead of the code.

I'm not going to attempt to write code that will monitor any and all changes in Sheet2 and update Sheet1 automatically. It's just not feasible.

Report •

August 30, 2009 at 21:19:20
Thanks Derby for your help. I will go by your suggestion.

Report •

Ask Question