Is it possible to do vlookup with criteria?

Hi Everyone,

Im not sure how to start this but i will start with my problem. I have two tables in one worksheet cointaning a couple of hundreds of account.

One of the Table has the Operator Code with their Fullname whereas the other has their Initial only and No Operator Code. I want to copy the OP Code into the second table using vlookup but the problem is they will never match as the other table has initials and surname only. Is there anyway this can be done at all?

I have attached a copy of the file.

Many thanks in Advance.

If you are as consistent as your samples show, then try:

=INDEX($H$6:$H$14,MATCH(LEFT(D6)&"*"&MID(D6,4,100),$I$6:$I$14,0))

copied down.

Or use this to extract the inital and surname

=LEFT(I6,1)&RIGHT(I6,LEN(I6)-FIND(" ",I6)+1)

to match column D

What happens if there is a duplicate name, ie John Jones and Jenny Jones will both be listed as "J Jones"

Originally Posted by NBVC

If you are as consistent as your samples show, then try:

=INDEX($H$6:$H$14,MATCH(LEFT(D6)&"*"&MID(D6,4,100),$I$6:$I$14,0))

copied down.

@ NBVC

Wow! you are amazing! I wish i have a brain like yours.

Would you mind explaining the formula for me if you dont mind please!

Thanks very much... you're a life saver!

Thanks... and you are welcome.

Well Index/Match works somewhat like Vlookup and is more flexible.. i.e. unlike Vlookup, you can have the lookup column on either side of the extracting column... also, you can indicate column to extract from by either referencing the single column in the index part or referencing a table, and indicating column by number of by matching the column title, etc...

So here we are indexing a column that is to the left of the actual lookup column.. so can't use Vlookup....

Then to find the Matching row, we use non other than the obvious MATCH() function which finds the position within a single dimension array that a match is found... and that allows you to correspond the position to the indexed range to pull the right value...

For matching, I used a wildcard between to know factors.. the Left(D6) simply extracts the Left-most character in the string.. the * means that you can have anything after that left character and the MID(D6,4,100) says the the part after the 4th character in D6 up to 100 characters must also match...

so for example,

MATCH(LEFT(D6)&"*"&MID(D6,4,100),$I$6:$I$14,0)

take J from Jennifer Jones and matches only the J, followed by anything (including just a space) and must end with Jones... and finds it in I6:I14.. and returns the position number.. then throws that number to the indexed array of $H$6:$H$14 and returns the corresponding match