![]() |
How to return a specific data point from a large array if I don't know the exact location?
Hi,
I have an Excel question and I'm not sure if I'm approaching it correctly. I have a column of data (C7:C8398) which is all text and I need to return the value given 3 conditions so I wrote the following array. =IF((A7:A8398=P7)*(K7:K8398=Q7)*(M7:M8398=0),C7:C 8398,FALSE) The value is a unique data point (e.g., there is only 1 data point in the entire column that satisfies all 3 conditions). For example, let's say the answer is "test". I don't know how to retrieve the value that I want because the result of the array would be: [FALSE, FALSE, FALSE, ...., "test", FALSE, FALSE...] I tried using INDEX/MATCH functions but I think that requires me to know what the answer is. I don't know the answer is "test". I have a whole column of answers (e.g., C7:C8398) but I don't know the answer that will satisfy the conditions. Any ideas? Am I approaching this incorrectly? Thanks in advance, Stuart |
How to return a specific data point from a large array if I don't
Try also, array-entered (press CTRL+SHIFT+ENTER):
=INDEX(C7:C8398,MATCH(1,(A7:A8398=P7)*(K7:K8398=Q7 )*(M7:M8398=0),0)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- " wrote: Hi, I have an Excel question and I'm not sure if I'm approaching it correctly. I have a column of data (C7:C8398) which is all text and I need to return the value given 3 conditions so I wrote the following array. =IF((A7:A8398=P7)*(K7:K8398=Q7)*(M7:M8398=0),C7:C 8398,FALSE) The value is a unique data point (e.g., there is only 1 data point in the entire column that satisfies all 3 conditions). For example, let's say the answer is "test". I don't know how to retrieve the value that I want because the result of the array would be: [FALSE, FALSE, FALSE, ...., "test", FALSE, FALSE...] I tried using INDEX/MATCH functions but I think that requires me to know what the answer is. I don't know the answer is "test". I have a whole column of answers (e.g., C7:C8398) but I don't know the answer that will satisfy the conditions. Any ideas? Am I approaching this incorrectly? Thanks in advance, Stuart |
How to return a specific data point from a large array if I don't know the exact location?
Public Function GetAnswer() As String
For i = 7 To 8398 If Sheet1.Range("A" & i).Value = Sheet1.Range("P7").Value Then If Sheet1.Range("K" & i).Value = Sheet1.Range("Q7").Value Then If Sheet1.Range("M" & i).Value 0 Then GetAnswer = Sheet1.Range("C" & i).Value Exit For End If End If End If Next i End Function This is how to do that Go to ToolsMacroVisualBasic Editor then Right click on the VBAProject, select INSERTModule then paste the code above in the coding area Back to your excel sheet. Goto to A1 then on the menu click Insert Functions On the insert function dialog, in the category select User define function and in the function list select GetAnswer Hope u understand my instruction. |
How to return a specific data point from a large array if I don't know the exact location?
thank you all for your help!
|
All times are GMT +1. The time now is 11:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com