Collect data from multiple sheets using Index/Match

Cliffu

New Member
Joined
Dec 19, 2019
Messages
3
Office Version
365
Platform
Windows
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
 

Attachments

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Todor

New Member
Joined
Oct 14, 2014
Messages
16
Office Version
2019
Platform
Windows
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

New Member
Joined
Dec 19, 2019
Messages
3
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 19, 2019
Messages
3
Office Version
365
Platform
Windows
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))))))
 

Watch MrExcel Video

Forum statistics

Threads
1,090,459
Messages
5,414,647
Members
403,540
Latest member
mmorejon1215

This Week's Hot Topics

Top