Sorting and Matching

[matthewparry45]

I am trying to find an Excel formula (or a string of formulae) to extract information (text and values) directly entered under specified category headings into matching categories within the same spreadsheet.

For example, I have the following information that I would like to extract from into other cells, based on the respective category heading:

Cell A1 Grains (this is a category heading)
Cell A2 Corn
Cell A3 Wheat
Cell A4 Rice
Cell A5 Oats
..
Cell A20 Oil (this is a category heading)
Cell A21 Butter Oil
Cell A22 Vegetable Oil
..

Cell A30 Protein
Cell A31 Beans
Cell A32 Fish
..

(the below figures are values that relate to the above items, such as Corn, Vegetable Oil, etc, but not to the headings themselves)

Cell B2 10
Cell B3 15
..
Cell B21 5
Cell B22 8
..
Cell B31 100
Cell B32 200

========

What I am looking for is a formula / string of formulae that will recognize when an item (such as Corn) is entered, and then display the text and corresponding values (under the same heading located at a different location on the same spreadsheet)

I know that I must specify the specific cell and range in which that text might appear (i.e., A1, A1:A5) but I cannot figure out the full formula.

I have tried VLOOKUP without any success - can anyone help?

Thanks,
Matthew

 

[Chris Davison]

One way would be to create a named range A1:B32 called (say) "database"

insert
names
define

name=database
range=$A$1:$B$32

okay

then, next to your input cells where you want the info to appear (let's say the first is cell X1) you could enter the following :

=VLOOKUP(X1,database,2,0)

and just copy it to all the other cells