Lookup for Data Range

[midfieldgeneral11]

Hope you can help.

I want a formulae to look into 2 separate spreadsheets.

The first is a master template list with a list 500 companies names in Column A (A2 - A500)

Distinct title list of types of business in column B1 to column AA1 in alphabetical order. Examples Like below. Going across

Aviation
Consultancy
Non Marine
Marine

The second list called Extract
Has the same 500 companies names in Column A (A1 - A500)
The types of business from column B1 to AA1 for each company are NOT in order. Like the two examples below

Two examples

Column A1 = Anna Insurance
Column B1 = Aviation
Column C1 = Non Marine
Column D1 = Cyber


Column A2 = Peter Insurance
Column B2= Commercial insurance
Column C2 = Cyber
Column D2 = Non Marine
Column E2 = Fine Arts

For each individual company we have examples of types of business's which are in both but on different columns. Like Non Marine in column C and in column D as per our examples.

What is required?
A formulae to be added to Master.
Where company in A2 = Anna Insurance
And B1 = Aviation
To populate B2 if that company has that type of business with a yes or no based on the following:

Look at extract spreadsheet look at Column A = Anna insurance
Where the range of Column B to AA and to flag with a yes or a tick that this company has this type of business across any cell in any tabs related to that company.

Does that make sense?
Hope you can help.

Simon

 

[mrshl9898]

Re: Lookup for Data Range - please help.

I'd just HLOOKUP in the rows on both sheets for that name, and return the name. Use LEN to see if there's a name being returned and use if to convert to YES.

Not certain on your sheet names, but assuming the companies are in the same order the below will do the job.

=IFERROR(IFERROR(IF(LEN(HLOOKUP(C$1,'Master Template List'!2:2,1,0))>0,"YES",""),IF(LEN(HLOOKUP(C$1,'Extract'!2:2,1,0))>0,"YES","")),"")