Asking for help getting vlookup to work

[eggman2001]

Hello,

I'm wondering if someone can help. Here is a workbook that I'm testing
out vlookup in:
http://download572.mediafire.com/itzzmmxemjjg/bmmyyemwkim/vlookup_book.xlsx

As you can see, column C has the vlookup functions and they all use
the text in the cell to the left of it as the lookup value and the
same table_array. C2 and C3 have found a match but C1 hasn't. The
lookup value for C1 and C2 look the same except that the one for C1 is
derived from a formula and the one for C2 is entered in manually.

My end goal is to get the vlookup in C1 to find a match just like C2.

Any help would be appreciated.

 

[Gord Dibben]

Sheet1 B1 is text while Sheet2 A1 is a number.

I would use in B1 =RIGHT(A1, 3)*1 to return a number.

Or preface Sheet2 A1 with an apostrophe.


Gord Dibben MS Excel MVP

 

[Max]

Think its better to do all the matching in TEXT in mixed data situations,
like what you have here ..

Try in C1, normal ENTER will do:
=INDEX(Sheet2!$B$1:$B$10,MATCH(TRUE,INDEX(B1&""=Sheet2!$A$1:$A$10&"",),0))
Copy down

The &"" bit will convert both the lookup values in Sheet1's col B, and the
reference lookup col values in Sheet2's col A to TEXT, enabling more robust
matching

Success? Celebrate it, ring the stars in google
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Shig]

How can I tell that B1 is a number and B2 is text? Is there a way for
me to convert everything to text?

It seems that the best way to go would be to convert everything to
text, but Max's formula is a bit difficult for me to understand (and
remember).

 

[Max]

How can I tell that B1 is a number and B2 is text?

Real nums appear right justified naturally while text nums will appear
left-justified. But the visual's not foolproof since formatting could earlier
have been applied (unknown to you) to say, left-justify the entire col.

Is there a way for me to convert everything to text?

As responsed earlier, concatenating the source data within the formula with
a zero length null string: &"" would be one way

It seems that the best way to go would be to convert everything to
text, but Max's formula is a bit difficult for me to understand (and
remember).

Aha, but it should'nt be, really. It's a basic Index/Match set to exact
match,
indicatively this:

=INDEX(ReturnCol,MATCH(LookupValue,ReferenceCol,0))

where
ReturnCol = the col whose values you want returned
LookupValue = the value to be looked up (ie matched)
ReferenceCol = the col to find the LookupValue
0 = set to exact match

The 2nd "Index" within the MATCH is used to enable the expression to be
normally confirmed (just press ENTER), instead of requiring array-entering
(CSE)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---