Thread: Finding data in a matrix

Is there a way in Excel to find data in a matrix using row and column references that are not numbers? I know that the index function works when the first row and first column are numbers; and lookup tables work in a vertical or horizontal direction with any values. What I guess I'm looking for is the equivalent of a combined horizontal and vertical lookup function.

Example: Let's say I have a matrix of total sales by state (first column of matrix) and industry (first row across the top of the matrix is a list of different industries). If I want to find total sales for the steel industry in Pennsylvania, is there some function that returns a sales value based on those two words -- e.g.,

Index(Pennsylvania,Steel) = $4.5 million?

Thank you. It worked. My apologies for posting in the wrong area. I thought I had posted to the Excel section, but I guess I didn't read it correctly.

Hello,

This information also proved very helpful to me, but I am in need of additional expertise. I would like to know if it's possible to apply an index function to generate individualized results (prices) for a large body of data.

Example: I have created an index for shipping prices based on weight (row) and shipping zone (column). In the second spreadsheet, I have 30,000 rows of individual/unique data (last year's actual shipments). In addition, the given information in the second sheet (weight and zone) are organized in two columns. So, I would like to find a function that will automatically link the proper price for each shipment so that I can calculate total prices spent on shipping last year.

Please let me know if this is feasible ASAP, and thanks in advance for any help!!

Note that the earlier posts in this thread are nearly 3 years old, so I doubt if anyone is checking them.

The general form is INDEX(table, row, column), so you can adapt the formula given above by DonkeyOte to suit your circumstances. It will be something like this:

=INDEX(Sheet1!$A$1:$Z$100,MATCH(C2,Sheet1!$A$1:$A$100,0),MATCH(D2,Sheet1!$A$1:$Z$1,0))

assuming that your index of shipping prices is in Sheet1, and that column C in Sheet2 contains the weight information and column D the shipping zone information. The formula would go somewhere on row 2 in Sheet2, and then be copied down to the bottom of your data.

Hope this helps.

Pete

Hi Pete,
Sorry for the lengthy response gap, but thank you SO much for this formula. It ended up working out, and helped me find employment!!

Also will make sure to post to newer threads in the future.

Thanks again!!