VLOOKUP to Match data using contain values??

[kenadams378]

Is there a way to use this function and lookup the cells that contain part of the data.

=VLOOKUP(A1&"*", ...etc


For example i'm looking for '3I Group' in a summary sheet, but in the data it shows '3I Group PLC'.

Is there a way to pull this info even though it's not a direct match?

[kenadams378]

I'm currently using this;

=VLOOKUP(L997&"*", Summary!$B2:$AA3050,18,)

But it's not returning any value, is there anyway to look up data containing rather than a direct match!?

[Cutter]

What is the value in L997?
If it's 3I Group it should retrieve data from column S for the first match of 3I Group* found in column B.
Try it with 0 after the 18,

[kenadams378]

Yes the value is 3I GROUP but adding 0 after 18 still doesn't return anything.

[Cutter]

Have you verified that there is a value in column S on the same row as the first match for 3I Group in column B?

[kenadams378]

Yes, i would expect the returned vlaue to be 'BBB'

I thought the look up was looking at the entire sheet not just one row?!

[Cutter]

What cell is BBB in (full cell ref inc sheet name) and what is in column B on that same row?
Your formula is looking from cell B2 to B3050 (on Summary sheet) for a match. If it finds one (and I assume it has because you haven't said it is resulting in a #N/A error) then it will return the value from column S (the 18th column from column B) on the same row as the first match found.
If this doesn't help it's time to upload a sample sheet with any personal/sensitive data removed.

[kenadams378]

BBB is in sheet name Summary and in cell Q.

There isn't a match as the reuslting data shows #N/A.

[kenadams378]

I've got some matches but still not convinced the formula is trying to match data when it contains certain values?

[wynfryth]

i have a question in relation to computation of grade, too: how could i format the excel computation of grade with with various flexible percentage distribution, transmutation out of the total score, and final view with unchangeable entry manipulation?

[jeffreybrown]

Hello wynfryth & welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[Cutter]

BBB is in sheet name Summary and in cell Q.

Your formula isn't going to retrieve that value even if it does find a match. Column Q is NOT 18 columns from column B - Column S is. Column Q is 16 columns from column B.

Visually find an expected match to "3I GROUP......." in column B of Summary sheet. If you find one in column B (and there are no other matches above it) then go across to column S (not column Q) and that is what your current VLOOKUP() formula will retrieve IF it agrees with you that the first 8 characters of the value in cell L997 (on sheet where formula is) are the same 8 characters in the value in column B.
VLOOKUP() returns #N/A when it can not find a match so the current formula is telling you that "3I GROUP....." does not exist in column B between rows 2 to 3050 on the sheet named Summary. If you're convinced that it is there then look for a leading space or different spelling - anything to cause the exact match on those first 8 characters to fail.

Is there a sample file coming?

[kenadams378]

The file i am using is currently 22mb, i'm trying to trim it down but getting to 1mb is rather difficult.

[kenadams378]

I think i've got it working now but i still need to tweak the look up to match anything containing a bank name for example;

Barclays PLC is the data i'm trying to match but my current criteria is Barclays Bank PLC, is there a way to look up this containing Barclays?

[Cutter]

You can try:

=VLOOKUP("Barclays *PLC",range,col count,0) or just =VLOOKUP("Barclays *",range,col count,0)

[kenadams378]

Is there something that can be used for all types of data?

[kenadams378]

I have a sample sheet with some sample cases,

The data is been matched to the summary sheet, i need to trim the stock descripion to just show the Name but then search for this name in the summary sheet to return the 3 highlighted columns, but the name has a number of variances, is there a way to search for a part of the name for example search for barlays and it matches the data conatining rather than an exact match?

[kenadams378]

Any ideas?

[Cutter]

Try this in cell C2 and down:
=VLOOKUP(LEFT(B2,FIND(" ",B2))&"*", Summary!$B:$AS,13,0)
that will search for the characters from the first, up to and including the first space

If you wanted to apply a minimum number of characters you could do it like this:
=VLOOKUP(LEFT(B2,MIN(5,FIND(" ",B2)))&"*", Summary!$B:$AS,13,0)

[san2525]

Dear Better you make a column that will be hidden showing a unique code for all entries ( 3-4 characters) then u can search for the required one, better you copy that part to a new sheet and attach here so that we can resolute it better.

please try the unique code hidden column.. i usually do alike.

sandeep

[kenadams378]

Apologies i'm not sure what you mean.

[Cutter]

I assume you mean post #20 and not my post #19.

[kenadams378]

Sorry Cutter, yes i did.

Your post is just great.

[kenadams378]

In this code;

If you wanted to apply a minimum number of characters you could do it like this:
=VLOOKUP(LEFT(B2,MIN(5,FIND(" ",B2)))&"*", Summary!$B:$AS,13,0)

Where do i apply the minimum amount of characters?

[Cutter]

The 5 within the MIN() function.

=VLOOKUP(LEFT(B2,MIN(5,FIND(" ",B2)))&"*", Summary!$B:$AS,13,0)

you can change that to a cell ref to be more flexible or just change it to any other #

[kenadams378]

Thanks Cutter