Excel 2007 - Range formula issue 2 sheets 1

[ITALIAORIANA]

Hi All,

I have a workbook with 2 different worksheets. CUSID's and CompanyTableHierarchy. I am trying to populate column BG on company table hierarchy with column A from sheet CUSID's. This is based on first 3 character matching subgroup values from both sheets. There are 59 rows on the company table sheet and 75 on the cus id sheet. So I am trying to get it to look at a range and find the match.


I have tried different variations of an IF statement but only the first 6 values populate.

=IF('CUSID''s'!C2=LEFT(U2,3),'CUSID''s'!A2,"NO MATCH")

=IF('CUSID''s'!$C$2:C$76=LEFT($U$2:$U$59,3),'CUSID''s'!$A3:$A76,"NO MATCH")

I also tried a lookup but I am not really familiar with how they actually work. And this gave me #N/A.

=VLOOKUP(U2,'CUSID''s'!A2:C76,3,FALSE)

Cus ID columns
A - cus id
B - company name
C - subgroup

Any help would be greatly appreciated.

Thanks
Deana

 

[SkipVought]

hi,

EXAMPLES please. Need data to work with.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Short answer, no IF statement! This is a lookup task.

I'd make a column C with the 3 charactrs required.

I'm TOTALLY in the dark regarding your company sheet structure???
[tt]
=INDEX('CUSID''s'!A:A,MATCH(Left(???,3),'CUSID''s'!C:C,0),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

Sorry about that, I've been working on this project for 3 straight days now, I need a break!!

Here are examples of the sheets I was given. I need to populate the CUSID field in sheet 1 with the cus id field value from sheet 2. This is based on the div value and the first 3 characters of the subgroup values.

SHEET 1 - Company Table Hierarchy tab info
Group Group Name SubGroup Class Plan Product RX Group ID Effective Term CUSID
EG00ABCD No Name University ABCD AQ01 EG100 EG100ABCD G74X1 1/1/2014
EG00XYZ1 Generic Hospital XYZ1 AV01 EG200 EG200XYZ1 G74X2 1/1/2014



SHEET 2 - CUSID tab info
Cus Id CLIENT DIV
6942 No Name University ABC
6953 Generic Hospital XYZ

Deana

 

[SkipVought]

Please put delimiters between column headings & column data elements. I have no idea what data is associated with what column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

I don't know how to do that.

 

[SkipVought]

Comma delimiters. I can deduce this one. The other one i cannot!

Cus Id, CLIENT, DIV
6942, No Name University, ABC
6953, Generic Hospital, XYZ


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

OMG! I am such an idiot!! Ok, I can do that. Term column is blank and CUSID is what I need to populate from the other tab.

Group,Group Name, SubGroup,Class,Plan,Product,RX Group ID,Effective,Term,CUSID
EG00ABCD,No Name University,ABCD,AQ01,EG100,EG100ABCD,G74X1,1/1/2014,
EG00XYZ1,Generic Hospital,XYZ1,AV01,EG200,EG200XYZ1,G74X2,1/1/2014,

Deana

 

[SkipVought]

I assigned Named ranges in your CUST_ID reference table, via 1) SELECT all cells in your table 2) Formulas > Define Names > Create from Selection -- Select names in TOP row.
[tt]
J2: =INDEX(Cus_Id,MATCH(B2,CLIENT,0),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

Thanks so much for your help!