Simple Find Values?

Ok so I got a cell value and I want to check if it exists in another table. Problem is the second table splits the value and moves the last 2 chars on the right into the next column like this :

4112JJPV

4112JJ PV



Ive tried using vlookup with a LEFT(cell1,6)
and a RIGHT(cell1,2) in place of other values but im having no luck.

I need to check for the first 6 characters then if the next column the value is equal to the last 2 character on the right I would like a True value returned.

I eve tried IF(AND(COUNTIF( but im just getting errors ect

any help please??


Thanks - James

You should be able to do it like this:

Please Login or Register  to view this content.

Where in this example;
A1 contained 4112JJPV

C1 contained 4112JJ
D1 contained PV

So C1 and D1 would be the cells in your 'other table' and A1 would be the cell you are comparing it to.

EDIT Ooops, sorry, I think perhaps I didn't quite understand the question, in which case the above will be no use to you. I don't think you can use VLOOKUP as that will only return the first instance of the first 6 characters it finds in your 'other' table. You would perhaps either need to add another column to your 'other table' that joins the 2 parts together again, and then check against that, or else use a VBA macro.

If we assume

Sheet1!A1: 4112JJPV

Sheet2!A holds 6 chars and B 2 chars

Sheet1!B1:
=ISNUMBER(LOOKUP(2,1/(Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100=$A1)))

TRUE means match found.

Originally Posted by DonkeyOte

If we assume

Sheet1!A1: 4112JJPV

Sheet2!A holds 6 chars and B 2 chars

Sheet1!B1:
=ISNUMBER(LOOKUP(2,1/(Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100=$A1)))

TRUE means match found.

D.O:

Would you mind explaining the breakdown of that LOOKUP for me please? Is it evaluating and comparing the whole range in one go somehow?

Thanks

Have a look at this similar thread in which I tried to provide a detailed explanation - hopefully it gives you the info you want ?

http://www.excelforum.com/excel-2007...-function.html

Thanks D.O, off to have a read