Forumla Displays??

Try formatting the cell as general

VBA Noob

Two things come to mind:

The cell is formatted as Text (Format->Cells...)

or

In Tools->Options->View->Window Options you have 'Formulas' checked.

Originally Posted by pba.mike

ok I attempted to accomplish this one on my own and I am stuck below is my forumla and it retruns #N/A? Any ideas????

Please Login or Register  to view this content.

b2:z100 is the array or range, a6, is one critera, b6 is another I am trying to get the result of matching a6 to the vertical colum a in sheet two and b6 to the horizontal row 1 and return the intersecting results??

Hi,

Try an amended second match as

=INDEX(Sheet2!B2:Z100,MATCH(Sheet1!A6,Sheet2!A2:A100,0),MATCH(Sheet1!B6,Sheet2!B2:B100,0))

or, having re-read your question,

=INDEX(Sheet2!B2:Z100,MATCH(Sheet1!A6,Sheet2!A2:A100,0),MATCH(Sheet1!B6,Sheet2!B2:Z2,0))

hth
---

Originally Posted by pba.mike

Below I have attached the worksheet (after figuing out that you could attach files) it's easier than trying to explain. sheet 1 I was trying to get the conductor area to match the values from column a in the associated row with a insulation type on sheet 2 column a (there will eventually be approx 25-50) and then match the conductor size with the top row of sheet 2 and return the value it intersetts with. (again there will be multiple rows of sizes) I used text because of sizes like 1/0 i did not want the decmial format does this make sense or is there a better way to accomplish this. thanks in advance for the help.

see the attached file for same result with OFFSET and MATCH combination.

Originally Posted by pba.mike

Below I have attached the worksheet (after figuing out that you could attach files) it's easier than trying to explain. sheet 1 I was trying to get the conductor area to match the values from column a in the associated row with a insulation type on sheet 2 column a (there will eventually be approx 25-50) and then match the conductor size with the top row of sheet 2 and return the value it intersetts with. (again there will be multiple rows of sizes) I used text because of sizes like 1/0 i did not want the decmial format does this make sense or is there a better way to accomplish this. thanks in advance for the help.

Apart from the Offset function shown by starguy, your formula was in error trying to match row 2 when the data was on row 1, also that the 14 is different on the two sheets, Text on Sheet1, Numeric on Sheet2. The Offset is probably more preferred option, but the Index would work as

=INDEX(Sheet2!B2:Z100,MATCH(Sheet1!A6,Sheet2!A2:A100,0),MATCH(Sheet1!B6,Sheet2!B1:Z1,0))

hth
---