Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Index Match?? | Excel Discussion (Misc queries) | |||
LOOKUP, MATCH, INDEX? | Excel Worksheet Functions | |||
index match lookup | New Users to Excel | |||
index?lookup?match?if? | New Users to Excel | |||
Lookup/Index/Match HELP! | Excel Discussion (Misc queries) |