View Full Version : Solved: lookup for part of a cell

tkaplan

02-18-2009, 09:11 AM

Good morning

I have 2 spreadsheets - sheet1 and sheet2

sheet 1 has columnA (for easiness sake, lets say rows 1 thru 100) with text values - mostly names of medications. column B has the price

Sheet 2 columnA has names of medications - but does not necessarily include all of the information the sheet1 columnA has.

I need to populate sheet2 columnB by looking to see if the first 10 characters of sheet2 cellA1 appears anywhere in the sheet1 columnA, and then give me the corresponding cost from column B.

I know that if I was looking at the entire cell values of Sheet1ColA I would do a vlookup. I just dont know how to do this looking at anywhere in the cell.

Any help would be greatly appreciated!

Try this array formula

=INDEX(Sheet1!$B:$B,IF(LEFT($A2,10)=LEFT(Sheet1!$A$2:$A$200,10),ROW(Sheet1! $A$2:$A$200)))

tkaplan

02-18-2009, 09:32 AM

Thank you. but wouldnt this search to see if the first 10 chars in col A sheet 2 match the first 10 chars in colA sheet 1? I need it to be the first 10 chars in col A sheet 2 occur anywhere in the cell in Col A sheet 1.....or I may very well be not understanding the formula....

for example

sheet 1

col A col B

soln albuterol 5% 28 mg $4

asthmanex inhaler $10

albuterol tab $6

sheet 2

col A

albuterol

so i want col B on sheet 2 to look at the first 6 chars of col A (in the real spreadsheet the names all have at least 10 so i'd do longer, but for the example above i'll use 6) and find the first instance in sheet 1 col A that it appears - row 1 and return col B ($4). but the "albute" can be anywhere in the cell of sheet 1 col A......

Thanks!

You mean that a cell in sheet 1 could contain other characters before and/or after the the 10 characters from sheet2?

tkaplan

02-18-2009, 10:38 AM

yes. if excel is not the right program for this I can take it into access but i'm not really sure how to do that either...

BrianMH

02-18-2009, 10:45 AM

Might be easier to do a macro. Are you just wanting to look up one value or a whole column? If a whole column will there be empty cells?

tkaplan

02-18-2009, 10:56 AM

whole column. about 400 values. no empty cells.

lucas

02-18-2009, 11:54 AM

post a small example with the results you want on another sheet......

=INDEX(Sheet1!$B:$B,MIN(IF(ISNUMBER(FIND(LEFT($A2,10),Sheet1!$A$2:$A$200)), ROW(Sheet1!$A$2:$A$200))))

tkaplan

02-18-2009, 12:59 PM

Here is a simplified spreadsheet of what I am trying to do.

thanks loads!

As I said before

=INDEX(Sheet1!$B:$B,MIN(IF(ISNUMBER(FIND(LEFT($A2,10),Sheet1!$A$2:$A$200)), ROW(Sheet1!$A$2:$A$200))))

works perfectly for the matching items, you just need to add a test for mismatches.

tkaplan

02-18-2009, 03:16 PM

when i plug the formula in, i get weird numbers......what am i doing wrong? (spreadsheet attached again with formula)

thanks!

That is because it is an array formula, and you have just Entered it.

Edit the cell, and instead of just hitting Enter, hit Ctl-Shift-Enter. You will see that Excel surrounds the formula in the formula barm with {...} - do not try entering these yourself. If you change the formula, you must again array enter it.

tkaplan

02-18-2009, 08:03 PM

Oops. didnt realize it was an array but now it works great! thank you so much!!!

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.