IINDEX and MATCH formula with more than one return value

[awest]

I am trying to combine information from three different reports from SAP that
I sent to excel files. I am using the INDEX and MATCH formulas to match
order numbers from each of these reports to get description, time worked,
employee name, part consumed and qty of part consumed for each order. My
problem is that multiple parts can be used for one order but when I do the
INDEX and MATCH formula I can only get a return for one of the three parts,
for example. Before I move on with combining these reports I wanted to see
if there is capabilty for excel to "insert a cell" so that multiple returns
of the formula can be matched to one order. Hope this makes sense. I
appreciate any help!

 

[Niek Otten]

I don't have the exact answer to your question, but maybe reading the link
below helps you on your way

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

 

[awest]

Thank you Niek, I did look at that link before but I don't think I can use it
in my application. Below is a simple version of what I am trying to do.

A B F G
Order Parts Order Parts
11 11 generator
22 11 shim
33 22 contactor
33 sensor


Column F & G have been copied from a report and inserted into my sheet, I am
trying to match the parts up to the order number in column A. I have other
data from 2 other reports that I am doing the same thing for but have
ommitted from this example to simplify. I was using INDEX and MATCH but my
problem is that Order 11 has two parts, the generator and shim. Is there
someway to get both of these parts to show in column B? Thank you so much for
your help!

 

[macropod]

hi awest,

See the attachments to my posts at:
http://www.techsupportforum.com/mic...fice-support/375768-excel-formula-issues.html

 

[awest]

Thank you Macropod--these formulas are what I was looking for. I was hoping
for a little more automation with my worksheet but I think this will work
great! I appreciate your help very much!

hi awest,

See the attachments to my posts at:
http://www.techsupportforum.com/mic...fice-support/375768-excel-formula-issues.html


--
Cheers
macropod
[Microsoft MVP - Word]

Thank you Niek, I did look at that link before but I don't think I can use it
in my application. Below is a simple version of what I am trying to do.

A B F G
Order Parts Order Parts
11 11 generator
22 11 shim
33 22 contactor
33 sensor


Column F & G have been copied from a report and inserted into my sheet, I am
trying to match the parts up to the order number in column A. I have other
data from 2 other reports that I am doing the same thing for but have
ommitted from this example to simplify. I was using INDEX and MATCH but my
problem is that Order 11 has two parts, the generator and shim. Is there
someway to get both of these parts to show in column B? Thank you so much for
your help!

 

[awest]

The file with your formulas work great if I have only one input line but I
have a lot of different orders I need to be able to get the information for
and I don't want to rewrite the formulas for each order. Is there a way to
get around this?

hi awest,

See the attachments to my posts at:
http://www.techsupportforum.com/mic...fice-support/375768-excel-formula-issues.html


--
Cheers
macropod
[Microsoft MVP - Word]

Thank you Niek, I did look at that link before but I don't think I can use it
in my application. Below is a simple version of what I am trying to do.

A B F G
Order Parts Order Parts
11 11 generator
22 11 shim
33 22 contactor
33 sensor


Column F & G have been copied from a report and inserted into my sheet, I am
trying to match the parts up to the order number in column A. I have other
data from 2 other reports that I am doing the same thing for but have
ommitted from this example to simplify. I was using INDEX and MATCH but my
problem is that Order 11 has two parts, the generator and shim. Is there
someway to get both of these parts to show in column B? Thank you so much for
your help!

 

[macropod]

Hi awest,

It'd take considerably more work to accommodate mutiple orders in the one table - if, indeed, doing so is possible. Alternatives you
might explore include setting up multiple query tables, or using a macro to sequentially input the individual order #s into the
query table then copy out & store the results elsewhere.

--
Cheers
macropod
[Microsoft MVP - Word]

The file with your formulas work great if I have only one input line but I
have a lot of different orders I need to be able to get the information for
and I don't want to rewrite the formulas for each order. Is there a way to
get around this?

hi awest,

See the attachments to my posts at:
http://www.techsupportforum.com/mic...fice-support/375768-excel-formula-issues.html


--
Cheers
macropod
[Microsoft MVP - Word]

Thank you Niek, I did look at that link before but I don't think I can use it
in my application. Below is a simple version of what I am trying to do.

A B F G
Order Parts Order Parts
11 11 generator
22 11 shim
33 22 contactor
33 sensor


Column F & G have been copied from a report and inserted into my sheet, I am
trying to match the parts up to the order number in column A. I have other
data from 2 other reports that I am doing the same thing for but have
ommitted from this example to simplify. I was using INDEX and MATCH but my
problem is that Order 11 has two parts, the generator and shim. Is there
someway to get both of these parts to show in column B? Thank you so much for
your help!

:

I don't have the exact answer to your question, but maybe reading the link
below helps you on your way

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

I am trying to combine information from three different reports from SAP
that
I sent to excel files. I am using the INDEX and MATCH formulas to match
order numbers from each of these reports to get description, time worked,
employee name, part consumed and qty of part consumed for each order. My
problem is that multiple parts can be used for one order but when I do the
INDEX and MATCH formula I can only get a return for one of the three
parts,
for example. Before I move on with combining these reports I wanted to
see
if there is capabilty for excel to "insert a cell" so that multiple
returns
of the formula can be matched to one order. Hope this makes sense. I
appreciate any help!