lookup H&V...or match...index???
 

Hi,

I probably have 30 or so columns and 200 rows. For simplicity if I have:

A B C D E F
1 Blue Green Red Orange Purple
2 100 2 4 6 3 5
3 200 8 9 5 7 2
4 300 5 8 1 3 2

The names of the columns are colors and the names of the rows are numbers
(and they are all known beforehand).

What formula do I need to retrieve the value of the cell that corresponds to
Orange and 200 (so I want to retrieve the number 7)?

Thanks!

lookup H&V...or match...index???
 

Try

=INDEX(A1:F4,MATCH(200,A:A,0),MATCH("Orange",1:1,0 ))

--

HTH

Bob

"Kevin W" wrote in message
...
Hi,

I probably have 30 or so columns and 200 rows. For simplicity if I have:

A B C D E F
1 Blue Green Red Orange Purple
2 100 2 4 6 3 5
3 200 8 9 5 7 2
4 300 5 8 1 3 2

The names of the columns are colors and the names of the rows are numbers
(and they are all known beforehand).

What formula do I need to retrieve the value of the cell that corresponds
to
Orange and 200 (so I want to retrieve the number 7)?

Thanks!

lookup H&V...or match...index???
 

It is either

=VLOOKUP(200,A:F,5,FALSE)
(5 being the 5th column in the range)

or

=HLOOKUP("Orange",1:4,3,FALSE)
(3 being the 3rd row in the range)

"Kevin W" wrote:

Hi,

I probably have 30 or so columns and 200 rows. For simplicity if I have:

A B C D E F
1 Blue Green Red Orange Purple
2 100 2 4 6 3 5
3 200 8 9 5 7 2
4 300 5 8 1 3 2

The names of the columns are colors and the names of the rows are numbers
(and they are all known beforehand).

What formula do I need to retrieve the value of the cell that corresponds to
Orange and 200 (so I want to retrieve the number 7)?

Thanks!

lookup H&V...or match...index???
 

Try this€¦

=SUMPRODUCT((C1:G1="ORANGE")*(B2:B4=200)*(C2:G4))

Or

Put this formula in I2 cell
=SUMPRODUCT((C1:G1=I1)*(B2:B4=J1)*(C2:G4))

In I1 cell type €œOrange€� and J1 cell type €œ200€�.

--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Kevin W" wrote:

Hi,

I probably have 30 or so columns and 200 rows. For simplicity if I have:

A B C D E F
1 Blue Green Red Orange Purple
2 100 2 4 6 3 5
3 200 8 9 5 7 2
4 300 5 8 1 3 2

The names of the columns are colors and the names of the rows are numbers
(and they are all known beforehand).

What formula do I need to retrieve the value of the cell that corresponds to
Orange and 200 (so I want to retrieve the number 7)?

Thanks!

lookup H&V...or match...index???
 

Take a look at Debra Dalgleish's site:
http://contextures.com/xlFunctions03.html

Especially example 2.

Kevin W wrote:

Hi,

I probably have 30 or so columns and 200 rows. For simplicity if I have:

A B C D E F
1 Blue Green Red Orange Purple
2 100 2 4 6 3 5
3 200 8 9 5 7 2
4 300 5 8 1 3 2

The names of the columns are colors and the names of the rows are numbers
(and they are all known beforehand).

What formula do I need to retrieve the value of the cell that corresponds to
Orange and 200 (so I want to retrieve the number 7)?

Thanks!

--

Dave Peterson

lookup H&V...or match...index???
 

Hi,
try

=INDEX(B3:F5,MATCH(I1,A3:A5,0),MATCH(I2,B2:F2,0))

index(B3:F5 is where the information to be retrieved is

match(I1, A3:A5 = I1 is where I enter 200 and A3:A5 the range where the
values are

match(I2,B2:F2 = I2 is where I typed orange and B2:F2 is the range where
your colors are

change ranges and cells to fit your needs
"Kevin W" wrote:

Hi,

I probably have 30 or so columns and 200 rows. For simplicity if I have:

A B C D E F
1 Blue Green Red Orange Purple
2 100 2 4 6 3 5
3 200 8 9 5 7 2
4 300 5 8 1 3 2

The names of the columns are colors and the names of the rows are numbers
(and they are all known beforehand).

What formula do I need to retrieve the value of the cell that corresponds to
Orange and 200 (so I want to retrieve the number 7)?

Thanks!

lookup H&V...or match...index???
 

....and one mo
=VLOOKUP(200,A1:F4,MATCH("Orange",A1:F1,),)
[While cell F4 us the lowest-right cell of the table]
Micky


"Kevin W" wrote:

Hi,

I probably have 30 or so columns and 200 rows. For simplicity if I have:

A B C D E F
1 Blue Green Red Orange Purple
2 100 2 4 6 3 5
3 200 8 9 5 7 2
4 300 5 8 1 3 2

The names of the columns are colors and the names of the rows are numbers
(and they are all known beforehand).

What formula do I need to retrieve the value of the cell that corresponds to
Orange and 200 (so I want to retrieve the number 7)?

Thanks!