Excel 2007 : Getting data from crosstable

[Chiemel]

Hello

I'm having a bit of trouble with extracting data from a crosstable in excell. Couldn't find any help through google, since I don't quite know how to formulate my problem.

So, my explanation:

I have an EOQ crosstable. In the left column I've got the amount of products sold of 1 category, and in the top row I've got the price/piece. The table is filled with EOQ values, the ideal amount I should produce according the the price/piece and yearly average sales.

\1 (the table)

On the next tab, I need the data extracted. This is where I got stuck. I tried horizontal and vertical search, but I couldn't get the right values.

So when I insert my AP (price/piece) and AFZ (yearly average sale), it needs to crossreference the table, and get me the result I wanted from the table.

To add some difficulty; the table has it's set value, and for example when I insert 70 for AP on the second tab, it needs to jump to 100 in the table, because it's more than the value before it, being 63. The same goes for the AFZ.

I hope this made some sense, and I hope someone can help me out a bit, or atleast help me on my way. I know there's easier solutions, but I want to get to know excel a bit better, and this is the way I want to do it.

Thanks for reading through this!

Chiemel

I know there's easier ways, but I want to get this table thing under control.

[NBVC]

Try:

=INDEX('EOQ Table'!$B$2:$K$14,MATCH(2,INDEX(1/('EOQ Table'!$A$2:$A$14>=C5),0)),MATCH(TRUE,INDEX('EOQ Table'!$B$1:$K$1>=C4,0),0))

[Chiemel]

Hey, thank you for your reply. I think I understand the biggest part of your formula, but there's still some errors in it I can't correct. The final error is a #name! error.

When I evaluate the formula, on the second step it changes the match into #name?, third step it checks the array against the given number, which is correct, then it gives some weird divide by zero error. On second thought, let me just post my new file with the error.

Thanks for taking a look at this NBVC.

[NBVC]

Don't put quotes around TRUE:

=INDEX('EOQ Table'!$B$2:$K$14,MATCH(2,INDEX(1/('EOQ Table'!$A$2:$A$14>=C5),0)),MATCH(TRUE,INDEX('EOQ Table'!$B$1:$K$1>=C4,0),0))

[Chiemel]

I don't believe that there are quotes around my true, this is my formula:

Please Login or Register  to view this content.

I had to replace your commas with ; probably because of my settings. Still getting the #name error. Been looking at this for the last 2 hours, driving me crazy :P Once again, thanks for helping out

[NBVC]

When I opened your file, I noticed single quotes around the TRUE... maybe you have to change TRUE to your language...

See the attached.

[Chiemel]

When I opened your file, I noticed single quotes around the TRUE... maybe you have to change TRUE to your language...

See the attached.

Thank you. Appearantly when I inserted the English terms it wouldn't work, but when I just opened your file it automatically translated the terms.
So thank you thank you thank you!

[NBVC]

Great. Btw, what language are you using and what was the translation for TRUE?

[Chiemel]

I was using Dutch, TRUE is "WAAR", Match is "Vergelijk".