How to look up a value in a list and return multiple corresponding values

Hi All,

I was wondering if someone could help me with an excel problem i am having. If you do offer help, you will need to explain things for a dummy.

I've treid looking on Online Office help but unless you understand their short hand on things it doesn't really help. For instance they mention (CTRL+SHIFT+ENTER) which i have no idea what they mean.

The problem i have is looking up references. For Instance if i have column A as a list of order numbers, then column F as a list of product codes that relate to the order numbers.

On another sheet i have a list of the similar order numbers, but what i want to do on this sheet is pull the product codes from the previous sheet through to this one by refering it to the order number...

For example

Sheet one
Order Number Product
05555 A
05554 B
05554 A
05553 A
05553 C
05553 B

Sheet 2
Order Number Products Following Column
05554 ??(should be B) ??(should be A)

Hope this makes sense?

Many thanks
Regards
The Rat

Hi,

Thanks, but VLookup only gives me 1 product code back for the order number, whereas the order number will have, say... 5 product codes listed to it. I need all 5 product codes to come back..

Regards

Hi,

Again, thank you. However a couple of things..

To get more than 1 return i have to copy down, what if i want to copy right. i.e i want to follow the same results horizonally along the row not vertically along the column?

Also, does this work if the data sheet you are looking up is on another worksheet (not workbook). eg. i have the data on sheet 3 and i want the results to come back on sheet 1?

Many thanks
Regards
The Rat

[Ctrl+Shift+Enter] indicates that you simultaneously press
the ctrl key (bottom left and bottom right of main alpabet keypad)
the shift key (marked with an up arrow and just above the ctrl key)
the enter key (also known as return, on the right just above the shift)
(at least that is where they are on my keyboard).

When I say simultaneously it is actually usual to press ctrl first, but keep it held down, then shift (keep them both down) then enter, and finally release them all.This combination tells Excel to treat the formula differently from normal.

Instead of assuming that there is a single set of values to be passed through the formula, [ctrl+shift+enter] indicates that there is a range (or array) of values to be passed through the same formula. The resultant formula is displayed in {brackets} to show its special status.

This is quite an advanced concept, which is tricky to get ones head round.
But if you can master it, there are things you can do which are hard or even imposible to achieve any other way.

Mark.

I think your problem could be solved using MATCH(), OFFSET() and INDEX() and additional "helper" rows, which might be easier for a novice to cope with than the tricky array formulas.

Assume Row 1 is the headings (order number; product 1; product 2...)
A2 not used
A3 ="05554"
B2 =MATCH($A3,Sheet1!$A2:$A1000,0) ... find the first match
C2 =MATCH($A3,OFFSET(Sheet1!$A$2,B2,0,1000),0)+B2 second match
Replicate C2 across as required for the number of products, thus:
D2 =MATCH($A3,OFFSET(Sheet1!$A$2,C2,0,1000),0)+C2
etc...
B3 =INDEX(Sheet1!$B$2:$B$1000,B2,1)
Replicate B3 across as required for the number of products, thus:
C3 =INDEX(Sheet1!$B$2:$B$1000,C2,1)
By replicate I mean select the cell, point to the bottom right hand corner, then click and drag.

Then copy and paste rows 2&3 onto rows 4&5 for the next order
and repeat for each order.

The key function here is OFFSET() which causes each subsequent MATCH() to start where the last finished.

Mark.