Quick excel help

[audi321]

Hi guys, I need to automatically populate a particular cell, depending on the inputs of 2 other cells. For example, if A1=Hello and B1=Today, then C1=Test

There are 4 dropdown options in A1 and 16 dropdown options in B1, therefore 64 permutations of C1.

Is it an IF or VLOOKUP I need? Or is there an easier option?

Thanks for any advice!

[21TonyK]

No real expert but I would use matching and indexing to pick the end result from a grid

[CubanPete]

You could do it a lot of ways. A nested vlookup would be easy.

[TuonoPants]

CubanPete said:

You could do it a lot of ways. A nested vlookup would be easy.

Or nested IF AND statements like this =IF(AND(A1="Hello",B1="Today"),"Test","")

[audi321]

Cheers guys, its a little more complex than I initial briefed.

Cell A1 has a value attached to it (eg "Hello" = 1, "Goodbye" = 2), and B1 also has a value (eg "Today" = 2000, "Tomorrow" - 3000) and C1 would add these together (eg Hello Today = "2001").

Sorry for not explaining fully to start with.

[Xerstead]

Are the values referenced in another chart/table, or is the number actually the values in the drop downs? If so just replace the 'Test' part of the IF formula with A1+B1 should work.

I'm thinking it's more complicated than that as you could just do a1+b1?

Edited by Xerstead on Tuesday 1st December 11:16

[audi321]

Xerstead said:

Are the values referenced in another chart/table, or is the number actually the values in the drop downs? If so just replace the 'Test' part of the IF formula with A1+B1 should work.

I'm thinking it's more complicated than that as you could just do a1+b1?

Edited by Xerstead on Tuesday 1st December 11:16

Yes the values are referenced in other columns. eg A1 - "Hello" then B1 = "1" and if C1 = "Today" then D1 = "2000"

In that scenario, E1 should equal "2001",

But likewise if A1 = "Goodbye" and C1 ="Today", E1 should equal "2002"

Edited by audi321 on Tuesday 1st December 11:28

[chip*]

The beauty of excel is the multiple options. Whatever option you decide, plan ahead and think how your data would or could change in time. If your look up list is small and values are static, a simple IF statement will suffice, but if the look up values are subject to constant change, a look up table could be easier to maintain.

[CaptainSlow]

Build a 4x16 matrix and use indexmatch...this is the best way.

eta
...and use the headings on the matrix as the data source for your drop downs.

Edited by CaptainSlow on Tuesday 1st December 11:48

[sideways sid]

SUMPRODUCT is probably the best function for this.

[paulrockliffe]

LOL, everyone has their best way.

I would add both tables to the Data Model, create relationship between them and create the output in a Pivot Table. It's 2020, we don't need bodgy formulas for simple things like this :-)