Check out some problems solved by our top Experts or click here to post your own problem for free!

index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return

Solved by E. H. in 60 mins

I want to dynamic access a sheet/cell from a closed file using INDEX. But when I try to "&A3&" for sheet name I forces me to select a file and then populates the sheet name instead of making it dynamically
INDEX('/Users/me/Desktop/["&A2&"]"&A3&"'!A:A,1,1)

Solved by G. H. in 20 mins

I'm trying to put the search and output lists for the following formula on a separate sheet. But simply selecting the data on that sheet won't work. =IFERROR(INDEX(E$2:E$6, AGGREGATE(15, 6, ROW($1:$6)*SIGN(MATCH("*"&D$2:D$6&"*", $A2, 0)), 1)),"")

Solved by X. W. in 31 mins

I need to use a lookup value that is the result of a formula, (in this case a date) and lookup or match an element in an array (which is a date.. same format as the resultant) and either access the value in the next right cell or use as an index to retrieve it.
I have used "=MATCH(C4,Demand,0)" in which C4 is a formula that returns a date in format as an example ... "16-Apr-18" .. and Demand is a series of columns with dates in the same format and sequenced ..
I get "#N/A" .. not the value I need and there is an exact date match in the lookup array ....

Solved by V. L. in 23 mins

I need to find an INDEX function that will convert the Call Day (a number 1-7) to the actual weekday found in row 1 of the DayofWeek named range. I don't know if I'm supposed to use the MATCH function as well or not.

Solved by D. D. in 11 mins

I need a formula (maybe Index and Match) to find a value in two different row

Solved by E. C. in 53 mins

I need a formula that looks for a specific name in three columns, takes all of the rows that match to get dates from another column. Then from the dates that match the rows with the names, take the second earliest date and generate it.
The below formula is giving me the second date listed for the specific person instead of giving me the second earliest date.
=IFERROR(INDEX('Input Data'!$E$5:$E$150,SMALL(IF(Tracker!$B6='Input Data'!$G$5:$I$150,ROW('Input Data'!$G$5:$I$150)-ROW('Input Data'!$G$5)+1),2)), "N/A")

Solved by A. J. in 34 mins

I'm having trouble with an index & match formula where I'm looking up two different pieces of info in order to return a specific value.

Solved by K. D. in 54 mins

could someone breakdown this formula for me :D =ArrayFormula(IF(D$5>=ROWS(B$5:B5),INDEX(Sheet1!C$6:C$21,SMALL(IF(Sheet1!D$6:D$21=$E$5,ROW(Sheet1!C$6:C$21)-ROW(Sheet1!C$6)+1),ROWS(B$5:B5))),""))

Solved by C. C. in 40 mins