Advanced Search and Value return

[HuXu77]

I have been working hours and days on this problem and can't seem to come up with a good solution.

I have an inventory spreadsheet that I have one column that has several different order numbers and serial numbers under each order number. In another column I have the serial numbers I have given out along with other info in the other columns. I need to figure out a way to take in a serial from the ones I have given out and find which order it belongs to and return that order number to a cell next to the serial number given out.

The closest thing I have to a solution is this

=CHOOSE(MATCH($C3, CHOOSE(1, $K$1:$K$18, $K$20:$K28, .. etc), 0), order1, order2, ... etc.)

Now this will work but I have to manually change the index_num of choose until I don't get a #N/A which is quite time consuming given that I do have 29 values in my choose.

Anyone have a better solution?

 

[schielrn]

Welcome to the Board!

Let me make sure I understand this, you want to find the value in C3, within column K it looks like.

But Column K is broken up into different orders?

Am I correct so far?

If so, where there is a break in the data (i.e. K19 in your sample) does it give the order number?

Is there any way the order number can be put out to the right of the data in column K and then you could do a vlookup?

From your sample it doesn't look like the orders are evenly spaced out amongst the rows, unless your sample data is not exactly accurate?

 

[HuXu77]

Right, the value will be $C3 where when I drag the equation to the other cells below it it will increase accordingly. The orders are all in column K and simply separated by a space and then the next order number. I could put the order number off to the side but would I have to put it next to every serial?

 

[schielrn]

I could put the order number off to the side but would I have to put it next to every serial?

Unfortunately yes.

But you said they are all separated by a space? I assume they are not all the same number of rows between each space?

If they are not the same, is it safe to build a formula around the assumption that if the data is in the section after the first space it is order2, then if it is after the 2nd space it is order3 and so on, since it doesn't appear you aheva first space before order1?