how to use vlookup to list data in multiple columns of a different sheet

[tony530]

I'm trying to do the data storage and a clear data display in 2 sheets, in which it will work as example below:

Sheet 1(details| revenue| outlay repeat about 20 times)

invoice| details| revenue| outlay| details| revenue| outlay| details| revenue| outlay...

IData1 |DData1|RData1 |OData1|DData2|RData2 |OData2|DData3|RData3 |OData3...

Sheet 2

invoice   IData1

details| revenue| outlay

DData1|RData1 |OData1

DData2|RData2 |OData2

DData3|RData3 |OData3

...

SumOfRData|SumOfOData

I used define&name and data validation to finish the invoice part, but I'm stuck at the [details| revenue| outlay], as the vlookup could not support such operation. Is there any way I can do this, showing the data according to the "IData" value?

Moreover, for some cases, those [details| revenue| outlay] may not be filled by 20 different items every time, sometimes may be only 4 to 5 would be filled, so any way make sheet 2 be smart, hiding the empty rows?

[#2]

@tony530 

Tony 

can you supply an example spreadsheet as it is difficult to understand your problem based on just the text.

Peter

[#3]

@peteryac60 

it's something like this, sheet1 store the data horizontally, and sheet2 display the data according to the job number selected in the drop-down menu.

sheet3 is the example using jn1 as the selected one

many thx

[#4]

@tony530 

Hi Tony,

I have come up with a possible solution using the OFFSET command and a rather convoluted formula - but it seems to work.

I am not sure how you get your source data (is it manual?) but you might want to consider changing the format to make it easier to manipulate? 

Anyway if you are happy with the solution please mark it as complete. 

many thanks,

Peter

[#5]

@tony530 

If you have 365, this is the way to go:

=SUBSTITUTE(INDEX(FILTER(Master!C:BJ,Master!B:B=List!$B$1),1,SEQUENCE(20,3,1,1)),0,"")