difficult lookup problem

[amartino44]

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]

Can do this one with a user defined function

Please Login or Register  to view this content.

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

[bentleybob]

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]

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

[amartino44]

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

[mrice]

Please see the attachment.

[amartino44]

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]

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.

[mrice]

An example of use across tabs is attached. The example also shows the use of cell references as opposed to literal values.

[JBeaucaire]

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