Hi all,
Liking the upload file option.
Have uploaded a file to demonstrate what I am trying to achieve, how I’ve thought would be the best way to start going about it and where it has hit a dead end.
The value in A9 determines what results I should pull back. How I would like those results displayed can be seen in N2, though I accept that might be a bit ambitious especially with the “and” before the final return. But first I have to get the results.
Ultimately the table I have to make this work for will be laid out like F1:K3, what is in A1:C6 is my working out how to use small, row and index to return my values, (http://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list). I have managed to get this working on A11:A15, (yet to wrap up in iferror but that feels like small potatoes right now).
Now to make it work when the table is flipped upside down and round about, F5:F9 works on the same idea except uses column instead of row and as can be seen does return the column numbers. Combining this with index as seen in G5 though gives me an error.
Any ideas on how to have this return the value of the cell 2 rows above what is in F3:K3 when the value in that range matches the value of A9?
As always, many thanks.
Liking the upload file option.
Have uploaded a file to demonstrate what I am trying to achieve, how I’ve thought would be the best way to start going about it and where it has hit a dead end.
The value in A9 determines what results I should pull back. How I would like those results displayed can be seen in N2, though I accept that might be a bit ambitious especially with the “and” before the final return. But first I have to get the results.
Ultimately the table I have to make this work for will be laid out like F1:K3, what is in A1:C6 is my working out how to use small, row and index to return my values, (http://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list). I have managed to get this working on A11:A15, (yet to wrap up in iferror but that feels like small potatoes right now).
Now to make it work when the table is flipped upside down and round about, F5:F9 works on the same idea except uses column instead of row and as can be seen does return the column numbers. Combining this with index as seen in G5 though gives me an error.
Any ideas on how to have this return the value of the cell 2 rows above what is in F3:K3 when the value in that range matches the value of A9?
As always, many thanks.
) else if you want them in separate cell, then it could be done with formula.