Conditional VLOOKUP?

[uberathlete]

Hi all! I've got two sheets, sheet 1 and 2 (attached). Basically, I nee
the Number column in Sheet 2 to be filled up with Sheet 1 as th
reference for the needed data. The problem is that some entries i
Sheet 1 have the same ID but are from different Groups. For example
AAA Inc. has an ID of AAA but can be in either LMFI_AAA or LMFII_AAA
So, I basically need some sort of VLOOKUP that recognizes the Group o
something like that. Any help would be greatly appreciated.

http://www.excelforum.com/attachment.php?attachmentid=3976&stc=1
http://www.excelforum.com/attachment.php?attachmentid=3977&stc=

+-------------------------------------------------------------------
|Filename: sheet2.JPG
|Download: http://www.excelforum.com/attachment.php?postid=3977
+-------------------------------------------------------------------

 

[swatsp0p]

Use concatenation to create your lookup value. In your example, on
sheet 2, A1 contains the 'I' group name, so:

=VLOOKUP($A$1&"_"&A4,Sheet35!$A$2:$D$7,4,0) returns 4444 in C4

copy this down your range of group I entries.

similarly, A9 contains the group "II" name, so:

=VLOOKUP($A$9&"_"&A12,Sheet35!$A$2:$D$7,4,0) returns 55555 in C12

Does this work for you?

 

[uberathlete]

^^ Thanks! I'll go try it out.

 

[David]

Seems you have stated your own answer, you need to look up the Group, since
it is the unique field. What is the problem in doing this?