VLookup/Data Problem

[taniab]

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

 

[Aladin Akyurek]

What is the formula you are running? Try doing an exact match.

 

[taniab]

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))))

 

[Aladin Akyurek]

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.)

 

[taniab]

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".

 

[taniab]

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?

 

[Aladin Akyurek]

Does the following resolve the issue?

=IF(C121="","",IFERROR(VLOOKUP($C121&" *",'Balance Sheet at 3108'!$A:$C,3,0),0))

 

[taniab]

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.

 

[Aladin Akyurek]

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?

 

[taniab]

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?