Vlookup with Multiple IF / Full name and abbreviation of Cie

KrazyAl

Board Regular
Joined
Sep 28, 2007
Messages
75
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
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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:
Code:
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:
HTML:
American Broadcast Company
this
that

you'll get:
HTML:
ABC
that

not sure if this is what you want.
 
Upvote 0

Forum statistics

Threads
1,212,139
Messages
6,106,179
Members
448,004
Latest member
Umberto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top