Vlookup with Multiple IF / Full name and abbreviation of Cie

[KrazyAl]

Hi!

I need to create a lookup that will look for two variable.

One page has the data and will always have the names written in long form (I.e. American Broadcast Company) while the top sheet will always show "ABC"

I need a lookup that will lookup for both American Broadcast Company and ABC.

Top Sheet
A1 = ABC
B1 = data in !DataSheet C1

Data Sheet
A1 = American Broadcast Company
B1 = Data
C1 = Data

I have tried different Vlookup however they don't accept two different names!

If it finds both it can give an error message, that should not happen. So I want to look for ABC and the lookup needs to find American Broadcast Company.

Thanks

FYI: Adding a new columns to add ABC etc is not possible

 

[cyrilbrd]

Hi,
not sure to understand what you wish to return.
would you like to return the value of 'Data sheet'!Cell C1 in 'Top Sheet'!Cell B1 if acronym and name are the same?

if yes and if the range are always know you could use this UDF:

Function FrstLtrs(str As String) As String
Dim temp
Dim i As Long

temp = Split(Trim(str))

For i = 0 To UBound(temp)
FrstLtrs = FrstLtrs & Left(temp(i), 1)
Next i

End Function

and as a formula use some such as:
=IF(FrstLtrs(Data!A1)=Top!A1,Data!A3,"")

so if:

American Broadcast Company
this
that

you'll get:

ABC
that

not sure if this is what you want.