Using if Statements to look up cell data (complicated)

[Ben1985]

Hi all

I'd like to ask whether this is possible. When I enter my sales data into a sheet it can be 10000 rows long, I want to be able to enter a set number of transactions on a second sheet which then uses a formula to look up what items was sold on said transaction.

I'm pretty sure it's possible but I'm out of my depth. I've using something like it before which was this statement - =IF($B1566="","",INDEX('RMS Sales'!P:P,MATCH($C1566,'RMS Sales'!$A:$A,0),1))

I've attached a example sheet whicih hopefully does a lot better job explaining that I am right now.

Any help is muchio appreciated.

Many thanks
Ben

For-Excel-Forum.xlsx

[sktneer]

On sheet2, in B2, try this Array Formula. Since this is an Array Formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down Ctrl+Shift and then press Enter)

Please Login or Register  to view this content.

and then drag across until you get blank cells.

Is this what you want?

[Tony Valko]

What data are you wanting to extract? The Item Number from column D or the Description from column E?

[Tony Valko]

Since the data is sorted/grouped together you can do it without the array formula.

[Ben1985]

On sheet2, in B2, try this Array Formula. Since this is an Array Formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down Ctrl+Shift and then press Enter)

Please Login or Register  to view this content.

and then drag across until you get blank cells.

Is this what you want?

Hi

Sorry I wanted the item Description (column E) to be dropped into B2. If there is more than one item then it would populate into C3 and so on.

[sktneer]

In that case the formula would be like this........

Please Login or Register  to view this content.

and then drag across.

Again an array formula so requires Ctrl + Shfit + Enter.

[Ben1985]

You're amazing thank you sktneer.

Ben

[sktneer]

You're welcome. Thanks for the feedback.

[Tony Valko]

Here's a non-array formula.

Entered in B2 and copied across until you get blanks:

=IF(COLUMNS($B2:B2)>COUNTIF(Sheet1!$A$2:$A$23,$A2),"",INDEX(Sheet1!$E$2:$E$23,MATCH($A2,Sheet1!$A$2:$A$23,0)+COLUMNS($B2:B2)-1))

[Ben1985]

Thanks Tony, what's the difference between an array and non array?

Ben

[Tony Valko]

It's kind of hard to explain.

See if this helps:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

Based on your application, a non-array formula would be more efficient (faster to calculate).

[Ben1985]

Hi Tony

Thanks for the input, much appreciated.

Ben

[Tony Valko]

You're welcome!