VLookup/Data Problem

taniab

New Member
Joined
Jun 8, 2015
Messages
42
So i want to Lookup "PL01053" in a table that has multiple records. However there is also a record that is "PL01053A", I don't want it to find that one,

I know the Vlookup formula will search until it finds the FIRST record that contains all that data.

how do I get it to exclude PL01053A and keep searching until it finds PL01053 please.

thanks
Tania
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I spent so long building it, I can't even remember what it means! Im a bit of a newbie so Im a little scared to mess with it in case I break it. From memory its saying,
if C121 is blank, don't do anything,
otherwise, look up the contents of C121 in the Balance Sheet spreadsheet and return the contents of the cell in Column 3.
If there is nothing in that column, then return 0.
I can't remember why the rest is there or what it means

Cell C121 in this instance is the value PL01053A, if it finds PL01053, then it returns the Column 3 results for that. If I replace the 0 with FALSE (for an exact match), it will sometimes return N/A is that right? or should I just put it in the part that is confirming the cell is not blank?

=IF(C121="","",(IF(ISERROR(VLOOKUP("*"&$C121&"*",'Balance Sheet at 3108'!$1:$65536,3,0)),0,VLOOKUP("*"&$C121&"*",'Balance Sheet at 3108'!$1:$65536,3,0))))
 
Upvote 0
1. What is your Excel version?

2. Do you really need using the * wildcard? (This makes that you pick up PL01053A when you are looking for PL01053 and the former occurs before the latter.)
 
Upvote 0
1. excel 2016

2. because the data in balance sheet actually has case number and name in the same cell. e.g. "pl01053 Smith" or "pl01053a jones".
 
Upvote 0
it's not always the same length either. sometimes it pl153a brown in the balance sheet. there is always a space though before the surname. Can it do a search of the full case number up to the space?
 
Last edited:
Upvote 0
Does the following resolve the issue?

=IF(C121="","",IFERROR(VLOOKUP($C121&" *",'Balance Sheet at 3108'!$A:$C,3,0),0))
 
Upvote 0
no that returns the data for PL1053a still. c120 has the case reference only. the balance sheet has the case reference followed by the surname in the same cell.
 
Last edited:
Upvote 0
no that returns the data for PL1053a still. c120 has the case reference only. the balance sheet has the case reference followed by the surname in the same cell.

There is a space in the look up value: $C121&" *". Did you notice that?
 
Upvote 0
no i didnt, my internet is down on the desktop so can't copy your formula. just had to type it into excel on the desktop from my phone. where exactly is the space please?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top