Combining VLOOKUP + IF Formulas?

[tsymonds]

Morning!

I am looking for a few different solutions (Apologies if I need to post multiple threads)

I have created a sheet that the end user will select from dropdowns in C2,D2,E2 which will give an outcome in F2 of either 'High Value' or 'Not High Value' (I have not added all options as will take hours I am just making an example sheet at this stage).

So I need the following.

a) Completed

b) If a Certain field is selected in C2 (E.G Aston Martin, Ferrari, Tesla), I need F2 to show 'High Value' regardless of what is selected in D2 + E2. This will remove the need to manually input every model for manufacturers that will always be High Value

c) If I select 'BMW' in C2, I would like only certain options to become available in D2. For example, If I choose C2, only 320, 325 + 330 are available in D2. As there are 000's of models to be input it will make the selection for the end user a lot quicker as it will shorten the list to scroll through

I appreciate all of these options on top of the formula in F2 may not be possible, but even 1 is it would be great!

[jason.b75]

Please do not edit your questions to remove any parts which you have subsequently solved. It can cause confusion to people who are trying to help you.
If you do solve your own question, in full or in part, we prefer that you reply to your own thread informing us of this and that you tell us how you have solved it so that anyone else looking at your thread trying to solve a similar problem is able to benefit from it.

[jason.b75]

For the "high value" output, assuming that the list to compare will only conatin the 'High value" manufacturers / models.

Create a new list of high value manufacturers (I assume those in red in the current list) in O2:O13, then use this formula in F2

Please Login or Register  to view this content.

You don't need vlookup because the result is the same every time.

For part c of your question, you need dependent data validation, there is a guide here to show you how it is done. https://www.contextures.com/xlDataVal02.html

[tsymonds]

Sorry about this Jason,

I had done what you said first time but I was doing it on my phone so it didn't post so I just took a shortcut!

[tsymonds]

Thanks for the help Jason.

I input this formula but just get a blank result.

I have created a new list H2:H13 of all high value vehicles that I need to always show as high value regardless of options chosen in D2 & E2

I'm not sure if I have done something wrong

[tsymonds]

Just to amend my previous comment,

=IF(ISNUMBER(IFERROR(MATCH(C2,$H$2:$H$12,0),MATCH(C2&" "&D2&" "&E2,$O$2:$O$288,0))),"High Value","")

This works perfectly so thanks Jason,

The only other thing I need that has been undone in this formula is if there is no Match in O2:288 then it needs to show as 'Capture'

The below formula is what I thought would work but Excel says it is too long

=IF(ISNUMBER(IFERROR(MATCH(C2,$H$2:$H$12,0),MATCH(C2&" "&D2&" "&E2,$O$2:$O$288,0)),"Capture",),"High Value","")

Any work around?

I appreciate this is getting quite long but I need it to be as fool proof as possible for end user!

[QUOTE=jason.b75;5083002]For the "high value" output, assuming that the list to compare will only conatin the 'High value" manufacturers / models.

Create a new list of high value manufacturers (I assume those in red in the current list) in O2:O13, then use this formula in F2

Please Login or Register  to view this content.

[bebo021999]

N,O,P column is Model Match separated into
Q column marks High-Medium-Low value
D2 & E2 is Data Validation of limited list (2 levels)
F2 geting value

[tsymonds]

Hi Bebo,

Thanks for your help!

It fixes one issue but then removes a fix for another issue I had!

I need to include if B2 is a particular option it shows as 'High Value' in F2

I can make a column with a list of these

Can this be included?

[jason.b75]

I haven't looked at the file in bobo's post to see what was suggested there. This should fix the issue that you mention in post #6 if I'm following correctly.

Please Login or Register  to view this content.

Were you able to resolve part c of your question from the link in my earlier reply?

[AliGW]

I have removed the SOLVED tag as the issue clearly is not fully resolved.