Need Excel2007 Formula Index, partial match

Closed
Sam - Sep 15, 2010 at 09:17 AM
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 - Oct 5, 2010 at 02:45 PM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 15, 2010 at 10:08 PM
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
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 29, 2010 at 10:21 PM
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
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Oct 1, 2010 at 10:17 PM
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
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?
0

Didn't find the answer you are looking for?

Ask a question
It costs money to open my file from speedyshare and I do not have it. please help.
0
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
0
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 491
Oct 5, 2010 at 02:45 PM
Hi,

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

Best regards
0
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.
0