Converting data in a lookup 1

[jamierobinson]

I would like to do a vlookup, or index/match, on data of one type to an array of data of another type. Example: the lookup value is text and the array is numeric. I need to convert both the lookup value and the array to the same type of data without actually having to reinput the data to effect a change. I would like to do something on the order of vlookup(value(C1),value(A1:B20),False), or the same thing using index/match. Any ideas?
-jamie

 

[SkipVought]

jamie,

Could you post an example of your lookup values and array?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[xlbo]

easy peasy

if you are looking up a NUMBER in a TEXT dataset, use

=vlookup(Text(Cell_Ref,"0"),LookupRange,2,false)

if you want to look up TEXT in a NUMBER dataset, use

=vlookup(Value(Cell_Ref),LookupRange,2,false)



Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[jamierobinson]

Thanks Geoff... But I also want to convert the array side of the equation. Any ideas?
-jamie

 

[KenWright]

All to numbers:-
=VLOOKUP(--C2,--($A$1:$B$20),2,0) array entered using CTRL+SHIFT+ENTER


All to Text:-
=VLOOKUP(""&C2,""&$A$1:$B$20,2,0) array entered using CTRL+SHIFT+ENTER

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

why do you need to convert the array side - you only need to convert 1 of the 2 sides to achieve a match...

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Hi Geoff - My guess is Dodgy data

Odds on that both sets of data have been imported and neither are 100% consistent in data type.

Regards
Ken.......

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------