Is it possible to do a VLOOKUP on Text and Numbers?

[CHBC1]

I prepare a report every month that consolidates account information from many different brokers. I have VLOOKUP's set up to pull in all the data from the different brokers' reports, but the issue is that some of the numbers are stored as text, whereas others are saved as numbers.

Right now I'm sifting through ~20 reports and manually converting certain numbers to text, so that they are all in a uniform format. This literally takes hours.

Is there a way to set up a VLOOKUP so that it can pull in both text AND numbers?? I've found formulas I can use to convert the VLOOKUP lookup_value to text, but that doesn't help in my situation, since there is a large data set of both numbers and numbers stored as text.

It seems like this may not be possible, but I figured I should ask.

Thanks!

 

[godsaaint]

I don't generally use vlookup, so a solution with index/match (which is basically the same thing) would be like this:

Array Formula(Ctrl-Shift-Enter): =INDEX(rangetopullfrom,MATCH(VALUE(lookup-cell/value),VALUE(rangetomatch),0))

EDIT: in case you are not familiar with index/match, this is a great link:

https://www.deskbright.com/excel/using-index-match/

 

[Marcelo Branco]

I think the best is correct your data

See if this helps
http://www.contextures.com/xlDataEntry03.html

M.

 

[etaf]

what is exactly in text/numbers
the value you are looking
the range you are looking up
the value you are returning

you can use *1 to change text number to a number
OR
value()


this changes the value to lookup
=VLOOKUP(A1*1,$G$1:$H$7,2,FALSE)
if its the range you are looking up then it should work just using the range
or
the value returned usually is a number returned

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) --
it accepts both numbers and numbers stored as text. Adjust references as needed.

=VLOOKUP(VALUE(A2),VALUE(Sheet2!$A$2:$K$200),2,FALSE)

 

[Derek73]

One way is to force a lookup as a number (A2*1), then if that returns an error, force it to lookup as text--lazy concatenate of nothing used below (A2&""):

=IFERROR(VLOOKUP(A2*1,LookupRange,Column#,FALSE),VLOOKUP(A2&"",LookupRange,Column#,FALSE))