PDA

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!

Bob Phillips
02-18-2009, 09:25 AM
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!

Bob Phillips
02-18-2009, 10:36 AM
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......

Bob Phillips
02-18-2009, 12:33 PM
=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!

Bob Phillips
02-18-2009, 01:55 PM
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!

Bob Phillips
02-18-2009, 04:28 PM
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!!!