Please help. Vlooup trouble

[Cooper_Smith]

Hello All,

Thank you in advance for your help. Here is the problem I am having. I am trying create a table that looks at one cell reference, finds that same text in a table, and returns an Id related to the number. The cell I am referencing has both txt and numbers (ex. 123 Company, Inc.).
The table that I am using for the TableArry in my Vlookup formula may not necessary have the exact same data (ex. 123 Company). The ID i would like it to return is a long string that includes both numbers and text as well (Ex. 00123141ABCDE).

I have been getting a number of errors. When the data is an exact match and I use (FALSE - Exact Match), the vlookup is returning an N/A error. When the data is an exact match and I use (True - Approximate Match), it returns that last cell in my lookuparray table. I read something about the Col_index_number needing to be smaller than the cell reference, but that is not the case here as I am using txt & number strings.

Should I use another formula? Or is their a way to fix this with a vlookup???

Thanks!

 

[ChrisR]

Can you post up several rows of data and the expected result and what you are actually seeing?

Thanks,
Chris

 

[Cooper_Smith]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account ID[/TD]
[TD]Account Owner[/TD]
[TD]Account Name[/TD]
[TD]Billing Street[/TD]
[TD][/TD]
[TD]Account Name[/TD]
[TD]Billing Street[/TD]
[TD]Account ID[/TD]
[TD][/TD]
[TD]formula[/TD]
[/TR]
[TR]
[TD]00001111ABCD[/TD]
[TD]Jon[/TD]
[TD]ABC Company 123, Inc.[/TD]
[TD]100 Test Avenue[/TD]
[TD][/TD]
[TD]ABC Company[/TD]
[TD]100 Test Ave[/TD]
[TD]002222DEFG[/TD]
[TD][/TD]
[TD]VLOOKUP(G2,A1:D3,1)[/TD]
[/TR]
[TR]
[TD]002222DEFG[/TD]
[TD]Kelly[/TD]
[TD]Test Company[/TD]
[TD]200 California Street[/TD]
[TD][/TD]
[TD]ABC Company, Inc.[/TD]
[TD]100 Test Avenue[/TD]
[TD]002222DEFG[/TD]
[TD][/TD]
[TD]VLOOKUP(F3,A1:D3,1)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test Company, Inc.[/TD]
[TD]200 California St.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test Company[/TD]
[TD]200 California Street[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The second column of "Account ID" are the results I am getting. They do not align with the actual account IDs/
Thanks!

 

[ChrisR]

Hi,

OK, so the reason you are getting an error is that you are saying you are trying to look for 123 Company, Inc on what sheet but then this could be 123 Co.Inc on another (I.e. different, as an example)

If so, using false will return #N/A as it needs to be an EXACT match...........Where as using TRUE will be unreliable, as it won't always be the closest match that you'd actually select yourself......

How different is your data between sheets? Are there any consistencies?

If so, for instance, you could do a search on the first five letters or something?

Enter the below and use CTRL + SHIFT +ENTER To enter it........It will only work if you do this.....You should see { } brackets appear automatically if it's done correctly.

=vlookup(left(G2,5),Left(A1:D3,5),1,FALSE)

Still not foolproof but might help you out with 90% of them......Worth a shot.

Regards,
Chris

 

[Cooper_Smith]

Hi,

OK, so the reason you are getting an error is that you are saying you are trying to look for 123 Company, Inc on what sheet but then this could be 123 Co.Inc on another (I.e. different, as an example)

If so, using false will return #N/A as it needs to be an EXACT match...........Where as using TRUE will be unreliable, as it won't always be the closest match that you'd actually select yourself......

How different is your data between sheets? Are there any consistencies?

If so, for instance, you could do a search on the first five letters or something?

Enter the below and use CTRL + SHIFT +ENTER To enter it........It will only work if you do this.....You should see { } brackets appear automatically if it's done correctly.

=vlookup(left(G2,5),Left(A1:D3,5),1,FALSE)

Still not foolproof but might help you out with 90% of them......Worth a shot.

Regards,
Chris

Reply >>

So it turns out the problem was the order in which the columns were placed. The column that the lookup values were in in the lookuparry needed to be before the column that I was returning the Ids from.

The problem still exists that the data is not consistent. Only about half are.

Thanks.