difficult lookup problem

**amartino44** · 06-11-2013, 03:02 PM

I've been spending an eternity trying to figure this out with no luck. I need a look up for the following example sheet that I've attached. I need to do a lookup on the second column but the values are not unique. The first column has unique values (and segments in column 2 correspond to these values) but they are only written once and I do not want to copy them. The other issue is that the report is constantly changing rows so I cannot just do a lookup on a segment. PLEASE HELP!!!!

For purposes of this example, let's say I want to get the value in C7 and use the value in column B (which isn't unique) as a lookup. Thanks.

**mrice** · 06-11-2013, 03:33 PM

Can do this one with a user defined function

Function LookupSpecial(Column1 As Range, Criteria1 As String, Column2 As Range, Criteria2 As String, ReturnColumn As Range)
Column1Row = Column1.Find(Criteria1, , xlValues, xlWhole).Row
Column2Row = Column2.Find(Criteria2, Column2(Column1Row), xlValues, xlWhole).Row
LookupSpecial = ReturnColumn(Column2Row)
End Function

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a suitable cell, enter = =LookupSpecial(A:A,"Department 2",B:B,"Bob",C:C) for example. The three ranges need to be entire columns.

Remember to save the workbook as a macro enabled workbook .xlsm

**amartino44** · 06-11-2013, 04:32 PM

This might work but it's giving me the "Name" error. I inserted a module in VBA personal folder but it's not working

**mrice** · 06-11-2013, 04:48 PM

Please see the attachment.

**amartino44** · 06-11-2013, 04:58 PM

Nice. Not sure what I was doing wrong. Now....Is there a way that I can refer to cells rather than hard coding "Department" and "Name"? In the tab I'm importing INTO I have a column with the lookup. I'm thinking about just writing Department hard coded but for the name I would like to just refer to the column/row. Is this possible? Thanks a lot.

**amartino44** · 06-11-2013, 05:00 PM

Also, does this work if I refer to a different tab? In the example I gave you everything was in one tab, but for the real thing, I will be using the lookup in one tab and pulling from the other tab.

**bentleybob** · 06-11-2013, 03:34 PM

With the goal of finding the name to the left of an entry in G1 (5 in your example):

=INDEX(B$1:B$12,MATCH(G1,C$1:C$12,0))

**amartino44** · 06-11-2013, 04:35 PM

bentleybob, what you wrote doesn't make sense (to me). I'm looking for the value in column C. Why do you say "5 in my example" referring to G1? Confused....If I put a 5 in G1 it gives me the value of Bob, but that's not what I need. I need the lookup to use the Bob (specific to a department) to get the value in Column C

**JBeaucaire** · 06-17-2013, 10:25 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.

difficult lookup problem

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

difficult lookup problem

Re: difficult lookup problem

Re: difficult lookup problem

Re: difficult lookup problem

Re: difficult lookup problem

Re: difficult lookup problem

Re: difficult lookup problem

Re: difficult lookup problem

Re: difficult lookup problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions