I want to get a value from an array, row 1 and column 1 known.

  • Thread starter rock_crushing_design_engineer
  • Start date
R

rock_crushing_design_engineer

say I have this ARRAY on a different sheet for reference:

LAG1 LAG2 LAG3
HE $45 $54 $102
QD $65 $88 $205
IO $102 $203 $405

If I input QD in one block and LAG2 in a different block as required inputs
on my input page, what would the equation be to return the value $88 into a
block on my pricing page? All ipages n the same workbook.
 
B

Bob Phillips

=INDEX(Sheet2!A1:D100,MATCH(J1,Sheet2!A1:A100,0),MATCH(J2,Sheet2!A1:D1,0))

where J1 holds QD, J2 holds LAG2

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"rock_crushing_design_engineer"
 
B

Bernard Liengme

With your table in A1:D4 of Sheet3 (cell A1 is empty; B1 is LAG1; A2 is HE,
etc)
and in another sheet cell A1 has LAG1 and B1 has QD then this formula
=VLOOKUP(B1,Sheet3!$A$2:$D$4,MATCH(A1,Sheet3!$A$1:$D$1,1))
returns $65.

Please note we talk about "Cells" not "blocks"; and "formulas" rather than
"equations".
Not being pedantic - you need to know the right words to make good use of
Help.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rock_crushing_design_engineer"
 
R

rock_crushing_design_engineer

Bob,

Thank You very much! I have been going through the help for 6 hours trying
to find out how to do this. Again Thanks!

Andrew
 
A

Alan Beban

Highlight your array. Click Insert|Name|Create and check Top row and
Left column.

Then =LAG2 QD will return $88;
or with LAG2 in J1 and QD in J2, =INDIRECT(J1) INDIRECT(J2) will return $88

Alan Beban
 
Top