Conditional VLOOKUP?

**uberathlete** · 10-31-2005, 09:50 AM

Hi all! I've got two sheets, sheet 1 and 2 (attached). Basically, I need the Number column in Sheet 2 to be filled up with Sheet 1 as the reference for the needed data. The problem is that some entries in 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 or something like that. Any help would be greatly appreciated.

http://www.excelforum.com/attachment...tid=3976&stc=1
http://www.excelforum.com/attachment...tid=3977&stc=1

**swatsp0p** · 10-31-2005, 10:19 AM

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** · 10-31-2005, 10:22 AM

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

David · 10-31-2005, 11:05 AM

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?
--
David

"uberathlete" wrote:

>
> Hi all! I've got two sheets, sheet 1 and 2 (attached). Basically, I need
> the Number column in Sheet 2 to be filled up with Sheet 1 as the
> reference for the needed data. The problem is that some entries in
> 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 or
> something like that. Any help would be greatly appreciated.
>
> http://www.excelforum.com/attachment...tid=3976&stc=1
> http://www.excelforum.com/attachment...tid=3977&stc=1
>
>
> +-------------------------------------------------------------------+
> |Filename: sheet2.JPG |
> |Download: http://www.excelforum.com/attachment.php?postid=3977 |
> +-------------------------------------------------------------------+
>
> --
> uberathlete
> ------------------------------------------------------------------------
> uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388
> View this thread: http://www.excelforum.com/showthread...hreadid=480574
>
>