Array formula

[HuskerBronco]

Hello guys...

I've got a formula in my spreadsheet that is quite lengthy. I could use an array formula to replace this formula...but I don't know how to construct it.

Below is an example of my current formula:

Please Login or Register  to view this content.

The code in my spreadsheet is far longer than this example, as there are considerably more options for each category.

Attached is a sample workbook containing the example above.
The worksheet might better explain what I need the array formula to do.

[NBVC]

Try constructing a table of values..

e.g.

In R2, enter "Cats", in R3, "Dogs", in R4, "Birds" and in R5, "Fish" (all without quotes)

Then in S1 to Z1, the numbers 1 to 8 across...

then in C1, use formula:

=INDEX($R$1:$Z$5,MATCH(A1,$R$1:$R$5,0),MATCH(B1,$S$1:$Z$1))

copied down.

You can add to your table and adjust formula as necessary..

The formula extracts the intersect of column A and column B values...

[HuskerBronco]

Thanks for your speedy reply...NBVC.

Going to try your suggestion, right now.

[PMO]

Hello,

A lead with a personal function.

Copy this code in a standard module

Please Login or Register  to view this content.

ArrayFormula(Cell1 As Range, Cell2 As Range)
Cell1 as range of animal's name
Cell2 as range of number

Example :
in A1 Cats, in B1 2 , in C1 =ArrayFormula(A1;B1) => Cats 2

With regards.

PMO
Patrick Morange

[HuskerBronco]

Holy Cow, Patrick...!!!

I don't know how you guys whip this stuff up so quickly.
I can't completely figure out what your macro is doing...but it works!!!

I had already incorporated a solution based on NBVC's suggestion into my spreadsheet...but, your solution was begging to be tried.
With a few modifications...your solution worked flawlessly.

Excellent, excellent work, guys....!!!