Lookup in Excel

J

Jim

Hello. Can anyone advise me how I might execute a lookup with 2 variables? Specifically, I want the value (contents) of a cell that is at the intersection of a number in a column, and and a number in a row. The column consists of ages 67 - 90, and the row is the same. The row/column numbers whose intersection I want will vary. For instance, I might want the value in the cell of (column) age 71 and (row) age 82, or (column) age 76 and (row) age 72, etc.

Thank you very much.
 
W

William

Hi Jim

1) I have the numbers from 1 to 21 in cells A3:A23 and the same numbers in
cells B2:V2.
2) Cells B3 to V23 contain various numbers.
3) Cell B1 contains the number 21 and cell C1 contains the number 11.
4 You want the value of the intersection where the value in the range A3:A23
equals 21 (cell B1) and the value in the range B3 to V23 equals 11 (cell
B1).

Try this formula:-
=INDEX(A2:V23, MATCH(B1,A2:A23,0), MATCH(C1,A2:V2,0))

--
XL2002
Regards

William

[email protected]

| Hello. Can anyone advise me how I might execute a lookup with 2
variables? Specifically, I want the value (contents) of a cell that is at
the intersection of a number in a column, and and a number in a row. The
column consists of ages 67 - 90, and the row is the same. The row/column
numbers whose intersection I want will vary. For instance, I might want the
value in the cell of (column) age 71 and (row) age 82, or (column) age 76
and (row) age 72, etc.
|
| Thank you very much.
| --
| Jim
 
J

Juan Sanchez

Jim

Say you have your column names on row 1:1 and your row
names on column A:A (name is ages from 67 to 90)

You have your searched column on A30 and your searched Row
on B30, on C30 type:

=OFFSET(A1,MATCH(B30,A:A,0)-1,MATCH(A30,1:1,0)-1)

You can change the searched column or row and the result
will change to the intersection of that row/column

Hope this helps!

Cheers
Juan
-----Original Message-----
Hello. Can anyone advise me how I might execute a lookup
Click to expand...
with 2 variables? Specifically, I want the value
(contents) of a cell that is at the intersection of a
number in a column, and and a number in a row. The column
consists of ages 67 - 90, and the row is the same. The
row/column numbers whose intersection I want will vary.
For instance, I might want the value in the cell of
(column) age 71 and (row) age 82, or (column) age 76 and
(row) age 72, etc.
 
W

William

Jim

Point 4 should have read....

4 You want the value of the intersection where the value in the range A3:A23
equals 21 (cell B1) and the value in the range B2 to V2 equals 11 (cell
C1).

Sorry!
--
XL2002
Regards

William

[email protected]

| Hi Jim
|
| 1) I have the numbers from 1 to 21 in cells A3:A23 and the same numbers in
| cells B2:V2.
| 2) Cells B3 to V23 contain various numbers.
| 3) Cell B1 contains the number 21 and cell C1 contains the number 11.
| 4 You want the value of the intersection where the value in the range
A3:A23
| equals 21 (cell B1) and the value in the range B3 to V23 equals 11 (cell
| B1).
|
| Try this formula:-
| =INDEX(A2:V23, MATCH(B1,A2:A23,0), MATCH(C1,A2:V2,0))
|
| --
| XL2002
| Regards
|
| William
|
| [email protected]
|
| | | Hello. Can anyone advise me how I might execute a lookup with 2
| variables? Specifically, I want the value (contents) of a cell that is at
| the intersection of a number in a column, and and a number in a row. The
| column consists of ages 67 - 90, and the row is the same. The row/column
| numbers whose intersection I want will vary. For instance, I might want
the
| value in the cell of (column) age 71 and (row) age 82, or (column) age 76
| and (row) age 72, etc.
| |
| | Thank you very much.
| | --
| | Jim
|
|
|
 
Top