Find value by looking two or more columns in other sheet.

Hello to all...

I have a table in which I want to find the a specific value in other sheet. The column Q is where I want the value to be shown. My criterias are column B or C.

See image1: PPlan2.png. This sheet is called "Models".

And the Matrix is located in another sheet of the same workbook called "PPlan" in column M.
See image2: PPlan1.png

From column R and forward in the PPlan Sheet are my conditions in different columns according to family.
For example, in row 11 my family is "DCH" which include "DCH-3XXX" and "DCH-64XX" models.

The tricky part is the following:
1. Compare the Models shown in the PPlan Sheet with the models of columns B and C of Models sheet.
Lets take as reference DCH-3XXX.
As you can see in image2, model in cell R11 must be compared with models in colums B and C (image1).
2. Return the corresponding model. It can be either in column B or C.
3. Return the value of the corresponing model in Cell Q22 of Model Sheet. In this case is 4200.
4. Continue with the next model "DCH-64XX" and repeat all the steps.
5. If more Models are found, repeat the steps untill no more models are to be found in that row.

I am stuck with this and is the last thing that I need to complete a report.

I know it's kind of difficult and tricky. I been searching with not enough good luck. Some solutions looked good but they use some conditions that didn't match my requirements at all.

I will appreciate all the help that you can give me. One more thing, I know that a VBA Code can do the job but I prefer formulas. Due to security setting in my office computer, I am not allowed to save VBA Codes.

Best regards and please have a great day.

SamCV.
Matamoros, Mexico.

Originally Posted by FDibbins

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

Having said that, you could try something like IFERROR(vlookup-to-search-in-B),vlookup-to-search-in-C)

Than you for the advice. I appreciate it.

I prepared an excel file to show what I need. I hope it can be of help.
If a formula can be kind of "head crusher", a VBA code can work. I will try to adapt it to the final report.

As I said first, I prefer formula.

Thank you.

SamCV.

Originally Posted by AZ-XL

Try this array formula to Q2 cell and hit Ctrl+Shift+Enter
=SUM(IF(ISERROR(SEARCH(C4,PPlan!$F$3:$AI$10)),0,1)*PPlan!$D$3:$D$10)

Hello AZ-XL!!!

I haven't the thread for a while. I reviewed your approach, and it it's great!!!

Great job, simple and to the point. I really really appreciate this great help. I had begin to use a pivot and I was worried because I had refresh every time. But with this formula, it perfectly suits my needs.

Thank you again and very sorry for the delayed answer. You have my gratitude.

Please have a great day and an excellent weekend.
(Que tengas un gran día y un excelente fin de semana)

SamCV.
Matamoros, Mexico.