2

I am creating a spreadsheet that a LARGE amount of people are going to use that have no experience with Excel...

What I want to have happen is they scan an order number into a field and it will populate the information on all lines of their order. When they scan, the scanner only populates the first 8 digits on the order, and it does not pick up on how many lines are on the data.

So for example; The scanner will return FK560082 but the data from the system will say FK560082.001.8051 and if there are multiple lines on the order it will have FK560082.002.8051 and etc... (We have no limit on the number of lines allowed an order).

Right now, I used the formula below to break the order number away from the other details.

=IFERROR(LEFT(A2,FIND(".",A2,1)-1),A2)

Which allowed me to use this formula to get my first occurrence (or first line) of my order. However, I'm looking for a formula that will allow me to find data from my other line items too. 

=IFERROR(INDEX('Current Orders'!F:F,MATCH('2'!A2,'Current Orders'!L:L,0)),"")

Since so many people are going to use this spreadsheet, I'd prefer to not have to train everyone on the ctrl+shift+enter of an array formula, but if that's all that's possible I'll make it work.

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
Melnemac32
  • 41
  • 3
  • I'm confused. Is FK560082.001.8051 in A2 or is it FK560082? –  Mar 20 '18 at 18:57
  • Sorry those formulas were between two worksheets! FK560082.001.8051 was in A2 for the first formula. I then switched to a different tab where I typed (or scanned in) FK560082 and it was pulling it from column L (which is the column I put the results from the first formula into) from the first tab. – Melnemac32 Mar 20 '18 at 19:04

3 Answers3

3

One or more FK560082.002.8051 values in 'Current Orders'!L:L. FK560082 in 2!A2. Additional information to be retrieved from 'Current Orders'!F:F.

Try,

=iferror(index('Current Orders'!F:F, aggregate(15, 6, row('Current Orders'!L$1:index('Current Orders'!L:L, match("zzz", 'Current Orders'!L:L)))/(left('Current Orders'!L$1:index('Current Orders'!L:L, match("zzz", 'Current Orders'!L:L)), len('2'!A$2))='2'!A$2), row(1:1))), text(,))

Drag down for subsequent invoice lines.

  • I tried that formula but it didn't work. It gave me a blank. :( – Melnemac32 Mar 20 '18 at 19:29
  • See the description of the way I set up my sample workbook added above. –  Mar 20 '18 at 19:33
  • What did you input as array for the aggregate function, @Jeeped? Does the division simply produce errors wherever the search fails? – elliot svensson Mar 20 '18 at 19:39
  • @elliotsvensson - Yes that's correct. The division produces a #DIV/0! error for everything that doesn't match. The 6 tells aggregate to ignore errors so only matching row numbers are returned. –  Mar 20 '18 at 19:42
  • Ok... So I revised the formula based, because one or more FK560082.002.8051 is in 'Current Orders'!A:A Do you see where I'm going wrong? =IFERROR(INDEX('Current Orders'!F:F, aggregate(15, 6, ROW('Current Orders'!A$1:INDEX('Current Orders'!A:A, MATCH("zzz", 'Current Orders'!A:A)))/(LEFT('Current Orders'!A$1:INDEX('Current Orders'!A:A, MATCH("zzz", 'Current Orders'!A:A)), LEN('2'!A$2))='2'!A$2), ROW(1:1))), TEXT(,)) – Melnemac32 Mar 20 '18 at 23:33
  • Try the workbook at [this link](https://1drv.ms/x/s!AiKOYgfOUhMWnxldtlpgTYbhJOZn). –  Mar 21 '18 at 00:27
1

I think this can be done using INDIRECT and by searching through Current Orders beginning after the first line that was found.

These search result formulas can be used on sheet '2' or any other sheet.

Separate your search results into two columns: the result, and a column for the row that was found. I'll use B. First item on the order, cell B4 formula is =MATCH('2'!$A$2,'Current Orders'!L:L,0)

Second item in column B (cell B5) will be =IFERROR(MATCH('2'!$A$2, INDIRECT("'Current Orders'!L" & $B4 & ":L9999"),0),""). Fill down from cell B5.

Column A will be the actual value, for instance at A4 =IFERROR(INDEX('Current Orders'!F:F, $B4),""). Fill down from A4 onward.

Good luck!

elliot svensson
  • 593
  • 3
  • 11
  • I'm a little confused as to what formula that I would put in my spreadsheet '2' Would it be the second formula? – Melnemac32 Mar 20 '18 at 23:37
0

Does this answer you question? [https://stackoverflow.com/a/18767728/9492960][1]

=INDEX('Sheet2'!B:B,MATCH(1,INDEX((A1='Sheet2'!A:A)*(C1='Sheet2'!C:C),0),0))

Is an Index Match with multiple criteria without an array.

JoeJam
  • 371
  • 1
  • 14
  • That could help. I'm just not sure how to set my criteria to search for multiple lines on the same order. Any ideas? – Melnemac32 Mar 21 '18 at 00:23
  • Are you searching for multiple columns in the same line so that you can return the correct order number? – JoeJam Mar 21 '18 at 17:55