Collect data from multiple sheets using Index/Match

[Cliffu]

Hi!
I´m kind of a basic excel user and struggeling with collecting data from different sheets.

First of, the Excel is in Swedish, so heres a short dictionary:

#SAKNAS! = NA
PASSA = MATCH

--------------------------------------------

What i want to do:

In my HEAD-tab is where I want all the data to be collected.

The MainInput-tab is where the main data is stored, but the problem is that the MainInput doesnt include all the neccesary data that I need.
So I also need to fill the Head-sheet with some data from SheetA,SheetB,SheetC.

I've made a simplified document to show what I´m aiming for.
As you can see I have no problem collecting data from SheetA but I have no idea how to keep it collecting from SheetB and SheetC as well.

The "Item" is unic and will only appear once in one of the sheets.

Hope that is enough info.

Thanks a lot!
Cliff

 

[Todor]

Hello,

First thing that I would suggest is that you convert all the data ranges into tables. This will make the data dynamic and easier to handle.

Second thing would be to use a nested INDEX/MATCH with IFERROR which will solve the issue of multiple locations.

Let me know how it goes.

 

[Cliffu]

Hello,

First thing that I would suggest is that you convert all the data ranges into tables. This will make the data dynamic and easier to handle.

Second thing would be to use a nested INDEX/MATCH with IFERROR which will solve the issue of multiple locations.

Let me know how it goes.

Beautiful, I´ll give it a go as soon as I have a few moments to spare.
Thank you

 

[Cliffu]

Thanks a lot @Todor, it worked like a charm!

OMFEL=IFERROR
PASSA=MATCH

Now it looks like this:
=OMFEL(INDEX('Blad1'!A:D;PASSA(A272;'Blad1'!A:A;0);2);OMFEL(INDEX('Blad2'!A:D;PASSA(A272;'Blad2'!A:A;0);2);OMFEL(INDEX('Blad3'!A:D;PASSA(A272;'Blad3'!A:A;0);2);OMFEL(INDEX('Blad4'!A:D;PASSA(A272;'Blad4'!A:A;0);2);OMFEL(INDEX('Blad5'!A:D;PASSA(A272;'Blad5'!A:A;0);2);INDEX('Blad6'!A:D;PASSA(A272;'Blad6'!A:A;0);2))))))