Look for offset value to the left from an array after only satisfying the match criteria of two sets first.

[ProgramUser]

Hi Excel Guru's, trust everyone is well today!!!....

I have been an avid reader of this excellent forum for a number of years and am a huge fan of Bill Jelen and Mike Girvin' work.....they have saved me many, many times. I can say the same for the many posters on this
website who have also unknowingly helped me. Thank you! As such, I rarely log in....only when I'm absolutely stumped. As for the title of my thread....I tried to offer the best descriptive name I could despite the
complexity of my requirement.

So without further adieu, I have nearly nailed my current problem however....after much deliberation and heartache trying to OFFSET(INDEX(MATCH to get the right result, I have sadly failed and am not sure where I'm going wrong.

Onto the procedure first;
I have one set of values in a single row ("AD22:AN22") that is orphaned to the far right of my worksheet. This can be referred to as the sample criteria, i.e. AD22:AN22 (ten numerical values across multiple columns in one row). I then need to assess (compare) for potential matches in a large array located at I35:S600 (again ten numerical values across multiple columns but as an array down many rows - call it, say...the sample set).

So, from there using COUNTIFS, this ascertains if there is a valid match across the array set I35:S600 to that of ten cells AD22 through to AN22 'the criteria'.

=COUNTIFS($I$35:$I$600,AD22,$J$35:$J$600,AE22,$K$35:$K$600,AF22,$L$35:$L$600,AG22,$M$35:$M$600,AH22,$N$35:$N$600,AI22,$O$35:$O$600,AJ22,$P$35:$P$600,AK22,$Q$35:$Q$600,AL22, $R$35:$R$600,AM22,$S$35:$S$600,AN22)
The resultant is either an 0 or a 1 which I'm using literally as a switch.

This bit is fine and works well!!

Then,

I need to report if there is a match using an IF statement such as =IF($AH$17=0,"No","Yes") which is where the 0 or 1 result from the COUNTIFS comes into play. Again, this works well...

The Requirement;
I now need to take any 'exact match' and look left one column (Column H) from the array set I35:S600 where the match was located and 'show the value' of that adjacent cell in another empty cell located at AF18. Naturally I abandoned the VLOOKUP option due to its inability to look left which led me to =OFFSET(INDEX(MATCH combination function.

I have tried (and isn't working);
=OFFSET(IF(COUNTIFS($I$35:$I$600,AD22,$J$35:$J$600,AE22,$K$35:$K$600,AF22,$L$35:$L$600,AG22,.......) with variations of =INDEX(Lookup_Value,MATCH(lookup_value_1&lookup_value_2,lookup_array_1&lookup_array_2, match_type) then ending with the IF statement to close out.

I haven't gone down the VBA road as yet, (I have a mountain modules within this same workbook), as it's not something that needs altering regularly or is hugely demanding on processor performance however,
I am open to both rigid FORMULA or VBA options.

In Summary;
Sample Criteria = "AD22:AN22"
Sample Set = "I35:S600"
Report MATCH OFFSET value = "AH18"

1. Compare Criteria ("AD22:AN22") with that of Set ("I35:S600") (This is working)
2. If a Match is found throw 0 or 1 as a switch using IF (This is working)
3. And if a match is found in Set ("I35:S600"), then look one column to the left of the set (now Column H) and find that corresponding adjacent value to the matched set ("I35:S600"), in that cell reference "Hnnn" and show this value at "AH18"

I hope I haven't confused you. It's nothing more than a simple silly OFFSET(INDEX(MATCH problem but I am stuffing it up somewhere!!!!!!

Looking forward to your response.


Sincerely,

' ##############
ProgramUser
' ##############
' I prefer * myself!

 

[ProgramUser]

Sorry: Should have attached a pic of spreadsheet or pic as a sample. Please see link in this message.
TinyUpload.com - best file hosting solution, with no limits, totaly free
Ignore the terms 'Barcode', it is merely a reference to the project and doesn't affect the solution I'm after.

The link is to a sample excel file that has all the criteria in it.

Please take a look and let me know your thoughts.

ProgramUser.

 

[Peter_SSs]

Firstly, your sample file does not match your written description in post #1 - most of the columns are offset by 1. My comments and formulas below relate to the sample file layout, not the written description above.

I would suggest using some helper cells to simplify the formulas. These helper cell columns could be hidden once the formulas have been entered if you want to keep your sheet looking 'tidy'.

1. In R7 (copied down to the the end of the data at row 394), concatenate the 10 columns with
=H7&"|"&I7&"|"&J7&"|"&K7&"|"&L7&"|"&M7&"|"&N7&"|"&O7&"|"&P7&"|"&Q7

2. Copy the formula also from R7 into cell AN22 to concatenate the "assessment row"

3. Simplify the formula in AH17 to
=COUNTIF(R7:R394,AN22)

4. Formula for AG17
=IF(AH17,INDEX(G7:G394,MATCH(AN22,R7:R394,0)),"")


Note for the future: You will get many more potential helpers if you post a small (copyable) screen shot directly in your post rather than provide an outside file link. My signature block below has help regarding that.

 

[ProgramUser]

Duh!.....Shouldn't code or play with Excel on Sundays. Seriously, sometimes the KISS principal just doesn't scream loud enough!!!!!!!!

Many thanks Peter, appreciate your time.

Thanks for pointing out the disparity between my long-winded explanation and the file, I'll keep that in mind for next post.

Thanks again and enjoy the rest of your day.
(and yes it worked like a charm)

 

[Peter_SSs]

Glad it helped. Thanks for letting us know.

 

[ProgramUser]

For those of you out there with a similar problem, here is the final worksheet with markups: http://s000.tinyupload.com/?file_id=62799261818495189380

Thanks goes to Peter_SSs

ProgramUser