Asking for help getting vlookup to work

  • Thread starter Thread starter eggman2001
  • Start date Start date
E

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.
 
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
 
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
 
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).
 
Shig said:
How can I tell that B1 is a number and B2 is text?
Click to expand...

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?
Click to expand...

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).
Click to expand...

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
---
 
Back
Top