• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help on index or lookup - not sure how to proceed here...

mediatx

Member
Hello Everyone


hopefully someone is able and willing to help me how to solve the following problem.

NO vb is possible, only formulas can be used.


Within sheet1 in column "labor" is a field with contents in various formats and lenghts like:


[Table1!A1 = Column:Labor]


PPTM quicksearch (where PPTM is a product)

Did some research on pqcr (where pcqr is a product)

info about prodcut1234 (where prodcut 1234 is a product)

...


In Sheet2 I have a pricelist (coming from another source)

I now need to take the (text)items from column "products" from the pricelist, search if the text in column "labor" on sheet1 matches the current searchterm/entry and if there is a match put the corresponding price on sheet2 column "price".


Can that be done?

It would safe me a day or two...


Thanks for any help on this.
 
not sure where I can upload here, but i put it on

http://www.4shared.com/file/sB3VQaV6/Sample.html


In Sheet1 in c1-C4 I need the prices from sheet2

So I thought it should be possible to look in Sheet2!A2:A6 if there is a match from Sheet1!A2:A4 and get the corresponding price displayed in sheet1!C2:C4
 
Hi, mediatx!


Looking at your file, in Sheet1 the data is very inconsistent. As far as I can see, there's no simple way to doing it with formulas, and I don't say it could be done from VBA, but it'd be easier.


A first approach -and depending on how many products are in Sheet2- may be:

a) use cells D in advance in Sheet1 for testing again each product in Sheet 2

b) copy with transposing cells A2:A6 in column A from Sheet2 to cell D1:H1 in Sheet1

c) in D2 type: =SI(ESERROR(HALLAR(MAYUSC(D$1);MAYUSC($A2)));"";HALLAR(MAYUSC(D$1);MAYUSC($A2))) -----> in english: =IF(ISERROR(SEARCH(UCASE(D$1),UCASE($A2))),"",SEARCH(UCASE(D$1),UCASE($A2)))

d) copy D2 to D2:H4 (in your sample file)


And if you get only one value in a cell in column <column> within each range in a row D<row>:H<row>, then it's highly probable that the product in A<row> is the product in A<column>.

If you get more than one, just check manually.

And if you get none, like for Sheet1 A3 value, check it manually too: mispelled in this case, "pqcr" instead "pcqr".


Good luck! You'll need it...

Regards!
 
Hi, mediatx!

I meant: "it's highly probable that the product in A<row> is the product in <column>1".

Sorry.

Regards!
 
Thanks for your help and even spending time on it.

The two problems i got:


1) I can not influence how the text is entered in "labor task"

2) I am using powerpivot to cleanup from several sources and merge, so i cant use transpose


So I hoped there is a way to create an arry taking all items from "product" in sheet2 as search term in each row within "labor task" and if there is a match put the corresponding price into the price coloumn/row of sheet 1.


:(
 
Hi, mediatx!

That would be an ideal solution... for an ideal data set. Which isn't the case.

If there's another approach to deal with your issue, honestly I am not able to even imagine it. Maybe others can figure out. Just keep on asking.

Regards!
 
would it be possible to use FIND within the MATCH function?

If so I think I could get the thing working with INDEX


Any idea?
 
ok, got it done!!!! :)


I inserted a new column in Sheet1 stripping of the product with a huge nestedIF, and then use index & match to get the price from the sheet2.


what a formula!!!!


Thanks anyway, that really helped me getting into the right mood to think how it could be done!
 
Back
Top