Help getting Index/Match to find search value (number) in a cell with multiple values (numbers)

Jay1

I have a sheet with the following formula in a column titled "Status":

=IF(ISBLANK([PO#]1), "Processing", IF(INDEX({Status Range}, MATCH([PO#]1, {PO Range}, 0)) = "Loaded", "OTR", IF(INDEX({Status Range}, MATCH([PO#]1, {PO Range}, 0)) = "available load", "Logistics Dept", IF(INDEX({Status Range}, MATCH([PO#]1, {PO Range}, 0)) = " ", "Processing", IF(INDEX({Status Range}, MATCH([PO#]1, {PO Range}, 0)) = "arrived", "Complete", "Processing")))))

It's intended to find the corresponding Status that's in the same row as the searched PO number (for example, 4500). It works fine when the PO Range in the target sheet only has one number, however if that cell had multiple values (1500, 4500, 3110), it returns a NO MATCH error. Is there a way to write this formula to be able to find the single 4-digit number in the search value among a group of 4-digit numbers and continue with the rest of the formula?

J. Craig Williams

Not easily.

A few observations / questions.

It appears you are returning 5 Status values from the Target Sheet and then translating them into 5 different Status values. Things get simpler if you add a column in the Target Sheet to do this translation, where

 "Loaded" = "OTR",                   

 "available load" = "Logistics Dept"

 " " = "Processing"               

 "arrived" = "Complete"

 (not found) = "Processing"

How are the PO#'s on the Target Sheet being entered?

Craig

 

Paul Newcome

You could write in a MID statement to pull 4 numbers at a time, but writing it to cover more than one 4 digit number will become rather lengthy. OR...

With the use of some Helper Columns you could separate it out and find the match for each 4 digit number individually. Then you would use a JOIN statement in the column you want to display the Statuses to pull all of your results together.

You may also want to consider making it an "=IF(JOIN(" statement basically saying to skip over any blank values in your helper column. That way you would be able to display "Status" if there is only one as opposed to "Status,,,".

"Hide" the helper columns once you've made sure it work to keep the sheet looking clean, and you should be set.

If you need any further help, don't hesitate to ask.

J. Craig Williams

My assumption was the 3 PO#'s was an example and it could be more (but how many more does not matter once the number is variable.

Craig

Paul Newcome

We must have been typing at the same time (I use the "hunt and peck" method. Haha).

 

I do agree with you though in that having the additional column on the target sheet would very much so simplify things.

Jay1

The PO#s in the target sheet are entered manually. As far as the translating column goes, would I input a formula along the lines of =IF(Status1 = "Loaded", "OTR", IF(Status1 = "Available Load", "Logistics Dept", "Processing"))?

J. Craig Williams

I was thinking even easier than that, create a new column and for each of the 4 statuses, enter the corresponding 'alias' for lack of a better term.

Here's what you are doing now:

1. find row containing PO#

2. determine [Status] on that row

3. from [Status] determine [Status Alias]

I propose instead

1. find row containing PO#

2. determine [Status Alias] on that row

....

That still does not solve the finding the PO#, but makes the solution simpler when we do.

Craig

 

 

 

Paul Newcome

What he said ^^^

A third column in your lookup table will simplify greatly.

I'm still working on an easier way to lookup multiple PO#'s in a single cell instead of using the helper columns to separate then a JOIN statement to put it back together.

This is going to bug me until it gets figured out.

J. Craig Williams

Questions for Jay1:

Your description had PO, PO, PO (comma separated) but data appears to be PO PO PO (space separated)

Will the data always be space separated?

4-digits_space_4-digits ... ?

Is there an upper limit to the number of PO's a cell (besides the 4000 character limit)?

Aren't you worried about someone overwriting the list that is already present?

What about a sheet that has 4 columns (one for each status) and the user enters the PO in the appropriate column, one per row?

That would be a lot easier than parsing the joined results.

Craig

Jay1

I've set up the helper columns as per Paul's suggestion and then entered a =value(mid formula to read the 4 digit numbers and separate the values and that's worked as intended, as well as the translation column in the target sheet so that my formula for the master sheet should now be =INDEX({Status New}, MATCH([PO#]1, {PO Range New}, 0)). {Status New} being the translation column and {PO Range New} being the Helper columns with the individual PO#s. However, the Status that is being returned is inconsistent in the master sheet. In the screenshot below the PO#s there are all in the helper columns on the target sheet and have one status associated with them in the Status Translation column, "OTR", but only the first value returns it. Could this be an issue in how the Index Match formula was written?

J. Craig Williams

I think {PO Range New} is malformed.

Is it multiple columns?

Can we get a screen shot of that sheet?

Craig

Jay1

The data will be space separated.

The limit will be 6 PO#s.

As for the overwriting issue the statuses from the target sheet match the master sheet, and the status column on the Master Sheet is locked.

The goal for the status column on the master sheet is to be as automated as possible, we'd like to avoid manual entry in other sheets.

Jay1

Here's the screenshot of the target sheet, the {PO Range New} consists of the PO 1-6 columns.

J. Craig Williams

The range is stored in what is called "column-major order", that is the number will be in this order:

[PO1]1, [PO2]1, ... [PO6]1, [PO1]2, [PO2]2, ....

Since you want to know the row and don't care where in the row it is, return the MATCH like this

=INT((MATCH( ...., {range}, 0) -1) / 6) + 1

For row 1, match returns 1-6. 

Subtract 1, so 0-5

INT (0 - 5) / 6 is 0

Add 1 to get 1

Regards,

Craig

Jay1

Okay, there was a slight issue with the Master Sheet's PO# column searching for blanks when empty in the Target sheet, however adding an IF(ISBLANK( to the formula in the Help Columns to replace blanks with an "x" resolved it. Afterwards, Craig's suggestion worked perfectly. Thanks everyone for your input, the sheet is working as intended.