Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match
M N O
X P 1 4 7 Y Q 2 5 8 Y P 3 6 9 These are Cells A1 to E4 How do I return a lookup that Y, Q, O = 8 Thank you Steven |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match
One way
=INDEX(A1:E4,MATCH(1,(A1:A4="Y")*(B1:B4="Q"),0),MA TCH("O",A1:E1,0)) entered with ctrl + shift & enter of course you should replace all hard coded values like Y, Q and O with cell references that way you don't have to edit the formula itself when you change the criteria -- Regards, Peo Sjoblom "Steven" wrote in message ... M N O X P 1 4 7 Y Q 2 5 8 Y P 3 6 9 These are Cells A1 to E4 How do I return a lookup that Y, Q, O = 8 Thank you Steven |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match
Try this:
=SUMPRODUCT((A2:A4="Y")*(B2:B4="Q")*(C1:E1="O"),C2 :E4) "Steven" wrote: M N O X P 1 4 7 Y Q 2 5 8 Y P 3 6 9 These are Cells A1 to E4 How do I return a lookup that Y, Q, O = 8 Thank you Steven |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match
Hi,
Here are two other approaches: =INDEX(E2:E4,MATCH("YQ",A2:A4&B2:B4,0)) and =SUMPRODUCT(--(A2:A4&B2:B4="YQ"),E2:E4) There is a major difference between these two formulas - the first one will only retrieve the first occurance of Y in column A and Q in column B, whereas the second one will find and sum all the entries in column E which meet the two criteria. If there will never be more than one row per condition then either formula works. To make it more flexible you can enter the YQ criteria in a cell and reference that cell in both formulas. -- Thanks, Shane Devenshire "Steven" wrote: M N O X P 1 4 7 Y Q 2 5 8 Y P 3 6 9 These are Cells A1 to E4 How do I return a lookup that Y, Q, O = 8 Thank you Steven |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match
Hi,
I found your formula while searching for an answer to a similiar problem, how would I write this to use the first two rows and first column rather than the first two columns and first row? Thanks Mike B "Peo Sjoblom" wrote: One way =INDEX(A1:E4,MATCH(1,(A1:A4="Y")*(B1:B4="Q"),0),MA TCH("O",A1:E1,0)) entered with ctrl + shift & enter of course you should replace all hard coded values like Y, Q and O with cell references that way you don't have to edit the formula itself when you change the criteria -- Regards, Peo Sjoblom "Steven" wrote in message ... M N O X P 1 4 7 Y Q 2 5 8 Y P 3 6 9 These are Cells A1 to E4 How do I return a lookup that Y, Q, O = 8 Thank you Steven |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |