How to match data in a table from another one?

Haseo1997 0
Viewed 19

here's the story.

I'm working on a project where I have several sheets in my Excel document. One sheet for one business.

In each sheet, there is a table like this one:

enter image description here

I have another sheet where I want to gather the data from all businesses. Here is the table I have in my "All" sheet:

enter image description here

I want to find an Excel function, or a combination of several functions to be able to complete this table using 'Order numbers'. What I mean is that I want the function to look at the order number in the "All" tab and be like:

"Ok, I have this order number 1512, if I go to the other sheet, I have the same order number, and for this order number, the order type is 'Delivery', so I return 'Delivery' in cell B2 in the "All" tab."

I hope it is clear, do not hesitate to ask if you want me to explain things a different way.

Same for 'Price' and 'Status'.

Thank you very much for your help!

spreadsheet function excel
1 Answers

multiple vlookups shall work with iferrors.

e.g. below formula to get "Order Type"

=IFERROR(VLOOKUP($A2,Business1!$A:$D,2,0),IFERROR(VLOOKUP($A2,Business2!$A:$D,2,0),IFERROR(VLOOKUP($A2,Business3!$A:$D,2,0),IFERROR(VLOOKUP($A2,Business4!$A:$D,2,0),VLOOKUP($A2,Business5!$A:$D,2,0)))))

  • Change 5 sheets with your business name (sheetnames)
  • for other two variables 'Price' and 'Status', just edit vlookup formula from 2 to 3 & 4 respectively.

Hope it Helps...

Sachin Kohli0
Related