Need Excel2007 Formula Index, partial match

Closed
Report
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
Hello,
I have 2 worksheets that looks like this: (Windows XP / Internet Explorer 7.0/ Excel 2007)
Sheet1: Site Information
ROWS COLUMNS
A(Site) B(City) C(C Manager) D(CM Cell#)
R2 016-125 Jonston =Frank Jones =(999) 999-9999
R3 016-138 Jacksonville =Frank Jones =(999) 999-9999
R4 020-100 Hampstead = Katie Lang =(910) 999-9001
R5 020-135 Jacksonville =Katie Lang =(910) 999-9001
R6 030-150 Holly Ridge =George Mikes =(910) 999-9002
R7 030-155 Maple Hill =George Mikes =(910) 999-9002
R8 030-201 Apple City =George Mikes =(910) 999-9002
R9 030-635 Radon =George Mikes =(910) 999-9002
R10 042-001 Wilton =Anthony Tie = (910) 999-9003
R11 174-361 Jones =Rod Thing =(910) 999-9004
R12 174-367 Jones =Sam Smith =(910) 999-9004
R13 174-420 Hampstead =Rod Thing =(910) 999-9005
R14 174-520 Jacksonville =Sam Smith =(910) 999-9006
R15 168-010 Borsch =Todd James =(910) 999-9007
etc.....
Sheet2: C Managers
ROWS COLUMNS
A(Partial Site/Site) B(C Manager) C(CM Cell#)
R2 016 Frank Jones (999) 999-9999
R3 020 Katie Lang (910) 999-9001
R4 030 George Mikes (910) 999-9002
R5 042 Anthony Tie (910) 999-9003
R6 174-361 Rod Thing (910) 999-9004
R7 174-420 Rod Thing (910) 999-9004
R8 174-367 Sam Smith (910) 999-9004
R9 174-520 Sam Smith (910) 999-9004
R10 168 Todd James (910) 999-9007

What I need is a formula for Sheet1: Column C(C Manager)and D(CM Cell#) to pull in the CM Names and Numbers from Sheet2. I need Column C (Sheet1) to have a formula to look at Cell A2 (Sheet1) to find a partial match of Cell A2(Sheet2), if finds the partial match to put the corresponding CM data. I put the correct answers in Sheet1 Column C and D. I have tried many different types of formulas but none come back with correct data. I even made sure both Column As are formatted text, not number
=INDEX(Sheet2!$B2:$B$10,IF(ISNA(VLOOKUP(VALUE((LEFT(A2,3)-1)),Sheet2!$A$2:$A$10,3,FALSE)),"N.A.",VLOOKUP(VALUE ((LEFT(A2,3)-1)),Sheet2!$A$2:$A$10,2,FALSE))) Answer I get is #VALUE!

=INDEX(Sheet2!$B$2:$B$10,SMALL(IF(ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,$A$2)),ROW($A$2:$A$15)-ROW($A2)+0),ROWS($C$2:C2))) I get Frank Jones for C2, #NUM! for C3-C15..

7 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
I have parked your file (sam.xls) in this web page. you download it.

http://www.speedyshare.com/files/24272150/sam.xls

(by the by hereafter you can use this web page www.speedyshare.com to upload your workbook)

there in sheet 1 see the formula in F2. copy this down and ALSO TO RIGHT

(NOTE CAREFULLY THE DOLLAR SIGNS SO THAT YOU CAN UNDERSTAND AND you yourself can CREATE SUCH FORMULAS)

columns F and G agree with your colmns C and D which I suppose you manually copied.

is this ok for you?
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
I think the file in speedyshare.com was deleted because of delay.

Perhaps I have not completely understood your problem. any how download the fiels sam.xls from the wbe page


http://www.speedyshare.com/files/24477532/sam.xls


see sheet1 column F and G. Is this result you want? if not what exactly you expect to have in sheet 1 may be given
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
download the file from here

http://www.speedyshare.com/files/24504745/Copy_of_sam_1_.xls

and see sheet1.

the formula in F2 is copied down and right.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

I tried pulling up the https://authentification.site/files/24272150/sam.xls but it would not pull up for me.. did I wait too long?
It costs money to open my file from speedyshare and I do not have it. please help.
The R2-R15 was the row names not part of the site name, when I remove that the formula still does not work.

I made the changes to the file to match my data examples.

https://authentification.site/files/24494720/Copy_of_sam.xls
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489
Hi,

Just change in the formula :
(LEFT($A2,3) by (LEFT($A2,0)

Best regards
Thank you so much this worked!
Only problem I had was trying to use another range.. but I did a work around!
I am very happy and so is my boss.