Lookup

[Stclements1]

I have a list of headings of which each has various subheadings, as per the example below. On my spreadsheet in Cell B3, I want to put the subheading i.e "Supplements" and I want Cell F3 to know that comes under ANIMALS (VETERINARY) and display ANIMALS (VETERINARY), but I'm not sure whether I would use LOOKUP for this?

ANIMALS (VETERINARY)

Medication

Supplements

Annual Vaccinations

Animal Transportation

Animal Hospitalisation

Vet Consultation

Vet Transportation

Animal Insurance

Parasite Treatment

ANIMALS (FOOD)

Chicken

Game (Alloa meat)

Game (H W Gutter)

Meat Handling Wear

Domestic Animal Feed

Dog Feed

Meat Preperation Equipment

Cleaning Materials

ANIMALS (HABITAT)

Teff

Hay

Enrichment

Dens & Platforms

Fly Catchers

 

[jasonb75]

Based on the assumption that the headers to return are the upper case ones.

Book1
	A	B				F
1	ANIMALS (VETERINARY)
2	Medication
3	Supplements	Supplements				ANIMALS (VETERINARY)
4	Annual Vaccinations	hay				ANIMALS (HABITAT)
5	Animal Transportation
6	Animal Hospitalisation
7	Vet Consultation
8	Vet Transportation
9	Animal Insurance
10	Parasite Treatment
11	ANIMALS (FOOD)
12	Chicken
13	Game (Alloa meat)
14	Game (H W Gutter)
15	Meat Handling Wear
16	Domestic Animal Feed
17	Dog Feed
18	Meat Preperation Equipment
19	Cleaning Materials
20	ANIMALS (HABITAT)
21	Teff
22	Hay
23	Enrichment
24	Dens & Platforms
25	Fly Catchers
Sheet1

Cell Formulas
Range	Formula
F3:F4	F3	=LET(r,MATCH(B3,$A$1:$A$25,0),rng,$A$1:INDEX($A$1:$A$25,r),LOOKUP(2,1/(EXACT(rng,UPPER(rng))),rng))

 

[SanjayGMusafir]

Your data look more like a MS Word kind of data.

Ideally in Excel it is recommended to put Headers and Subheader in separate columns - This way you get more control over your data and use it in different ways.

The way Jason explains above can be a way to do but not an ideal situation to control your data.

 

[Stclements1]

LET(r,MATCH(B3,$A$1:$A$25,0),rng,$A$1:INDEX($A$1:$A$25,r),LOOKUP(2,1/(EXACT(rng,UPPER(rng))),rng))

Many thanks for getting back to me, unfortunately, I get an error message each time I try to use the formula that says "The first argument of LET must be a valid name" am I entering the data incorrectly?

 

[jasonb75]

Have you tried to edit the formula in any way? There is no reason for that message to come up if you're entering it exactly as posted.

The first definition isn't strictly necessary and can be moved as shown below but the formula still works with it in there.

=LET(rng,$A$1:INDEX($A$1:$A$25,MATCH(B3,$A$1:$A$25,0)),LOOKUP(2,1/(EXACT(rng,UPPER(rng))),rng))

 

[Stclements1]

Once again many thanks for coming back to me I do appreciate the help. I have copied and pasted the formula into my spreadsheet and also typed it in and both times I get an error message, now it is saying "You can't include a parameter to a LET function after defining its calculation" I haven't altered anything from what you sent.

 

[jasonb75]

Could you confirm that you are using Office 365 (not an alternative like google sheets, or another version of Excel).

Also, is your device set up in a language other than English, or with settings where a comma is used as a decimal separator instead of a dot / period?

I've checked both formulas in case there was a problem when I copied them to the forum and both are perfectly fine so there is something else causing the problem, it's just a case of finding what that is.

 

[Fluff]

As well as Jason's questions, are you using A1 or R1C1 notation?

 

[Stclements1]

Thank you for your continued help, I am using office 365 and I am certain that my version of Excel is the most up-to-date one. I use ; as a seperator. I have copied and pasted the formula into my worksheet and as per the previous messages, I get the two error messages. I have also typed it directly into the worksheet and again I get the error messages. I believe that I am doing everything as I should so I really don't know why it is returning these messages.

 

[Stclements1]

As well as Jason's questions, are you using A1 or R1C1 notation?

I am not sure what A1 or R1C1 notation is, all I can say is that I am using an excel package that is no different from any earlier versions in the way that I input data.