while using the index and match formula, if the first result is not satisfied how to apply for the second result.
to make my question clear please check the included excel file.
while using the index and match formula, if the first result is not satisfied how to apply for the second result.
to make my question clear please check the included excel file.
Maybe this in F2 and down?
=INDEX($B$1:$B$7,AGGREGATE(15,6,(ROW($B$2:$B$7))/((E2=$A$2:$A$7)*($B$2:$B$7="found")),1))
In F2 then Copied across.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
thanks for the effort, i got to learn new things.
but this was not what i was looking for.
what i just wanted is,
while using index and match it searches in top to bottom method. so, while i use the formula "=index("result_array",match("20300192",$A$2:$A$7,0)).
in $A$2:$A$7 contains two same values "20300192". where first row of "result array" contains the value "not found" and second-row contains some other value,
so i don't want "not found" but the next row result which has other values but not "not found"
so, i want to discard the row which contain "not found" only if there are two same values like "20300192". if not then "not found".
This is exactly the resolution I gave you.
The expected result and formula result are same. Pl see file. If any problem pl upload file showing the problem. Pl see my previous post. In F6 you are getting Not Found, because you don't have Found for that number
20300817 in original data, from the formula.
Last edited by kvsrinivasamurthy; 11-24-2020 at 01:09 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks