Returning value from table subject to multiple criteria and conditions

[Bufford]

This problem is driving me nuts -- I'm trying to rewrite this without nested IFs and use SUMIF, SUMPRODUCT, or some MATCH/INDEX combination instead.

In English : What is the formula for C if I want it to return the label in H given the combination of matching A to Column E, then whether B is >= F and B < G?
excel_help.png

As an example, in Row 14. The process I'm looking for:

1) Does A14 match a value in Column E? TRUE/FALSE
2) On which row in the table is 1) TRUE, AND B14 >= F, B14 < G? (Note: the F&G test must be adjacent on the same row) Answer: row 4. Therefore, H4's text "vd3" is the desired output.


THANKS!!!

[jewelsharma]

try this formula in cell C2:

Please Login or Register  to view this content.

[Bufford]

Thanks, I appreciate it. Unfortunately, it didn't work when I applied it to the actual table in my work book, as the sample for the question was an abbreviated version. Also ran into trouble trying to incorporate a third condition for the search -- so I need formulas for C and E. The other bug was trying to use different sheet references with that $F$ operator -- A-E is on sheet1! and H-N is on sheet2!.

excel_help2.png

[AliGW]

Why not attach the workbook itself?

[samba_ravi]

pls attach a sample excel file instead of image

[Bufford]

My apologies for first submitting simplified lists -- I was trying to glean the general technique, then apply it to more complex situations. So here's the beast:

I need formulas for Data! columns C, E, and G.

Data! Column C: Match value from each row in Data! column A with Table! column A. Then where same row's Data! column B is >= Table! column B and < Table! column C, return Table! column D. (First match OK)

Data! Column E: Match value from each row in Data! column A AND column C with Table! columns A and D, respectively. Then where Data! column D >= Table! column E and < Table! column F, return Table! column G. (First match OK)

Data! Column G: Match value from each row in Data! columns A, C and E with Table! columns A, D and G respectively. Then where Data! column F >= Table! column H and < Table! column I, return Table! column K.


Many many thanks!

[jewelsharma]

Must easier to provide a potential solution once the complete details are provided. See attached. ExampleBook-Solution.xlsx

In most LookUp scenarios,

• Identify a value in the table that is unique to each row. In your case, i found it to be the last column SV Type.
• If the unique value is non-numeric, I suggest inserting a column at the start as a serial number to each row. (Much easier to work with numbers).
• Develop a formula to match the data conditions to the unique value (or row serial number).
• Then simply apply VLookUp on the unique value (or row serial number) and return the other desired values from different columns.

The approach I took is:

• Created an additional column at the start to give a serial number to each unique combination of values.
• Use SumProduct formula to return the unique serial number for the SV Type, based on the numerous <=, > criteria..
• Apply VLookUp to the unique serial number and return the SV Type from Column G of Data! worksheet.
• Manipulate the String in Column G to get the desired values for Column C and Column E. (Alternatively, you can apply VLookUp formula on the unique serial number and return values from the vd% and VSC columns respectively).

Note: Some values appeared as 0.00 in Column J (SV<) that i felt were not in line with the trend. I've amended and highlighted those in the attached sample workbook. Please review the changes.

Cheers!

[Bufford]

Cracked! Nice work, thank you. Serial very helpful!