VLOOKUP with RIGHT function not working

[Zabman]

Hi,

I have a list of data that I need to cross reference against some other data. The problem I am facing, is I only have the last 4 characters of what I will be searching. I have tried this:

=VLOOKUP(TEXT(G28,0),RIGHT(currentData.csv!$B:$B,4),1,FALSE)

and I can see it is assessing the last 4 digits by looking at the function arguments, but it is returning an #N/A error. I have confirmed the data is definately in the series I am doing the vlookup on. The data I am searching has 13761 rows if that could be where the issue is happening?

 

[mikerickson]

Use wildcards
=VLOOKUP("*abcd", currentData.csv!B:B, 1, False)

will return the first entry in column B of sheet currentData.csv that ends in "abcd"

 

[T. Valko]

Hi,

I have a list of data that I need to cross reference against some other data. The problem I am facing, is I only have the last 4 characters of what I will be searching. I have tried this:

=VLOOKUP(TEXT(G28,0),RIGHT(currentData.csv!$B:$B,4),1,FALSE)

and I can see it is assessing the last 4 digits by looking at the function arguments, but it is returning an #N/A error. I have confirmed the data is definately in the series I am doing the vlookup on. The data I am searching has 13761 rows if that could be where the issue is happening?

What version of Excel are you using?

Are you entering that formula as an array?

What's in G28?

What do you think this is doing:

TEXT(G28,0)

Can you post a few examples of the data that's in currentData.csv!$B:$B?

 

[Zabman]

Hi,

I modfied to reflect this:

=VLOOKUP("*" & TEXT(G93,0),currentData.csv!$B:$B,1,FALSE)

but it still wouldnt work - am not sure what I have done wrong

 

[mikerickson]

Is the data in column B text or numbers?

 

[Zabman]

Hi,

The data I am searching in is numbers formatted as text, the text(G28,0) is formatting this to text. I have to do this for some vlookups on other spreadsheets I have going. (if there is a better way to do this then I'm all ears :D)

I am using office 2010. The formula is not entered as an array (I think?)

<TABLE style="WIDTH: 58pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=77 border=0><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 58pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=77 height=20>

4753394[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753395[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753396[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753397[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753398[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753399[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753400[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753401[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753402[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753403[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753404[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>[FONT=Calibri]4753405[/FONT]</TD></TR></TBODY></TABLE>
[FONT=Calibri]

all formatted as numbers in the csv.

 

[Zabman]

The data in column b is numbers, but when i did the right() function it converts to text so I wouldnt see this as an issue

 

[mikerickson]

In that case, try

=VLOOKUP("*1234", currentData.csv!$B:$B&"", 1, FALSE)

entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Zabman]

we're in business thank you!!!

is there somewhere you can point me to learn about the ctrl shift enter?