Looking up a value with multiple matches in a table

[antaralib]

Hi,

I have a data table where the look up value was a name. When I would select a name (using data validation), it will show me all the related details mapped to it.

E.g the layout is like this:

Select Name1, I get the following details:

Start Date --Stdt1 StDt2 StDt3
End Date --- EDt 1 EDt2 EDt3
Item ID---- I1 I2 I3
Event------ E1 E2 E3

When I select Name2, I get:

Start Date --Stdt4 StDt5 StDt6
End Date --- EDt4 EDt5 EDt6
Item ID---- I4 I5 I6
Event------ E4 E5 E6

For this I have used the formula: -
=IF(COLUMNS(Sheet3!$C5:C5)>$G$3,"",INDEX(Sheet6!$E2:$E163,SMALL(IF(Sheet6!$A2:$A163=Sheet3!$F$3,ROW(Sheet6!$E2:$E163)-ROW(Sheet6!$E$2)+1),COLUMNS(Sheet3!$C5:C5))))



Now my question is, I want a different lookup value(start date this time) and a vertical layout.e.g When I select StartDate1, I should get:

Name EndDt ItemID Event

Nme1 EDt1 I1 E1
Nme2 EDt2 I2 E2


My formula doesn't work. Pls advice.

[Special-K]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[antaralib]

Appreciate your prompt response.

I want the present output but with a different lookup value and in a vertical layout.
have attached the mock one.

[antaralib]

I think there's some mistake in the cell reference when I am using the formula for a vertical layout. Unable to catch it.

Can someone think of a better solution. will be of great help.

[antaralib]

ok I got the values now just by replacing columns below: however, STILL NOT BE ABLE TO FETCH EACH AND EVERY VALUE from the table. Few at the end just return 0 or NULL.

=IF(COLUMNS(Sheet3!$C5:C5)>$G$3,"",INDEX(Sheet6!$E2:$E163,SMALL(IF(Sheet6!$A2:$A163=Sheet3!$F$3,ROW(Sheet6!$E2:$E163)-ROW(Sheet6!$E$2)+1),COLUMNS(Sheet3!$C5:C5))))

with rows
=IF(COLUMNS(Sheet3!$C5:C5)>$G$3,"",INDEX(Sheet6!$E2:$E163,SMALL(IF(Sheet6!$A2:$A163=Sheet3!$F$3,ROW(Sheet6!$E2:$E163)-ROW(Sheet6!$E$2)+1),row(1:1))))

[JeteMc]

I believe that it would be easier to help if you would manually input the values into columns A:F on the 'Required output' sheet. That way we can attempt to write formulas/code that will replicate those values.
Let us know if you have any questions.

[antaralib]

Any thoughts!!!

[antaralib]

Sorry. I did not quite get that. THe required output sheet contains the formulae to fetch the data from the data sheet. That's where I am going wrong.

[JeteMc]

It would help us to see what should go in the cells, not just the #Ref errors that are there now. Please manually type the expected results into each of the cells in the range A6:F7, on the required output sheet, so that we can try to modify the formulas that you have written (or write new formulas) so that they will yield the same results as the ones you type.

[antaralib]

My bad!1 Please check if this helps.

[JeteMc]

Select A6 on the required output sheet and paste the following into the formula bar:

Please Login or Register  to view this content.

After activating by simultaneously pressing the Ctrl, Shift and Enter keys you can copy across and down.
Let us know if you have any questions.