Sorting and Matching

matthewparry45

New Member
Joined
Jul 21, 2002
Messages
18
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
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
 

Forum statistics

Threads
1,144,510
Messages
5,724,777
Members
422,576
Latest member
kayth891

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
Top