match/lookup formula for multiple small tables

[deltanforde]

Hi
I am starting a new post hope that is okay, i have multiple small tables with varying numbers of rows and columns and i want to lookup the result where the row matches a certain number and the column matches a certain category. I can collect the individual identifiers for the rows and columns and I want to make one large table
excel example of rearranging small tables.xlsx

i would probably need a formula to look up the number in say column B but then to look up the category code in a range and then return the point at which number and category code meet?
Any ideas greatly appreciated!

[Tony Valko]

I am starting a new post hope that is okay

Does that mean you have another thread on this subject?

If so, you should probably continue with that thread. At a minimum, you should post a link to that thread so we can see what has already been discussed so there's no duplication of effort.

[deltanforde]

thank you - yes i thought it was solved but the offset function did not work - sorry i have only been on here once - any idea how to link to a previous post?

[Tony Valko]

Open the thread.

Copy the URL from your browser's address bar.

Then just paste that URL into a reply in this thread.

[deltanforde]

http://www.excelforum.com/excel-form...rge-table.html

sorry the reply here was an offset function - but i found another formula somewhere that uses index and match functions do you think these would work in my example?

[Tony Valko]

This is your bottom table:

Data Range

	B	C	D	E	F	G	H
19	Transform above data into this:
20		a1	b2	c3	d4	e5	f6
21	123456	60
22	234567
23	345678
24	456789
25	567890
26	678912
27	78913

We can use a formula to get the data in B21:B27.

In the file you note:

I can get a standard list of these headings

I assume that means you will manually enter the column headers?

So, if looking up the combination of 123456 and A1 you have a result of 60 but there is no combination of 123456 and A1 in either of the smaller tables.

[Tony Valko]

Also, you have duplicate column headers in both smaller tables so that complicates things even more.

This looks like a nightmare!

[deltanforde]

hi yes sorry i was trying to put a lookup formula in but it did not work - the result for that cell would be zero as there is no match.
and for the data i can get a list of all category titles from somewhere else i think / hope x

[deltanforde]

yep - and the duplicates are in different columns so i cannot even match them up

[Tony Valko]

You can test to see if the lookup criteria are in one table or the other.

How many of the smaller tables are there?

If there's "a lot" then a formula to do this might not be practical.

[deltanforde]

Yes I have a few thousand rows of data - however i have the same data in a different format that may be easier to work with?
I have had a look and if i just leave out all the categories and just collect data from a new column which i also have i think that would work, here is an example of what i am trying to do I WILL UPLOAD IT NOW - sorry caps lock on

[deltanforde]

here is the same data but presented differently so maybe a formula to match/index or lookup to find the total column may work?

i will be more than happy if i can just get number and total matched up in separate columns?

[deltanforde]

hi tony i will start a different post as this is a new query i think