Multiple IF's Formula Help!!

**msiyab** · 03-07-2016, 12:37 PM

Hi All,

Is it possible to retrieve 3-4 different inputs based on 3-4 different data in another cell.

For example,
I have various 3-4 types of expenses in Column F (Salik Charges, Hire Charges, Vehicle Fine & Fuel Charges). These 4 expenses have 4 different codes under which it needs to be categorized.

I require the codes to be retrieved in column L from a formula based on the data (expenses) in Column F.

Codes to be retrieved by formula:
Salik Charges 170.110.000.415030.0000.0000.000.000
Hire Charges 170.110.000.415025.0000.0000.000.000
Vehicle Fine 170.110.000.143015.0000.0000.000.000
Fuel Charges 170.110.000.415015.0000.0000.000.000

Thanks

**Admin** · 03-08-2016, 09:08 PM

The question is not clear at least for me

Can you please elaborate in detail ?

**msiyab** · 03-09-2016, 12:21 PM

Originally Posted by Admin

The question is not clear at least for me

Can you please elaborate in detail ?

If Column F contains the expense data (for ex. "Salik Charges"), I need a formula where it will bring up the respective code (i.e., 170.110.000.415030.0000.0000.000.000) in column L.

and so on for the 3 other codes & expenses.

**DocAElstein** · 03-09-2016, 07:51 PM

Hi msiyab,

_ I wasted a lot of time at the start of my “Excel carrier” doing multiple Ifs. If you really do want to do it that way then you simply do nested If’s

Pseudo Code:

= If ( x , 1 , ~~~ if ( y , 2 , …………… and so on ) ~~~ )

In XL up to 2003 that will work for up to 7 If’s. For XL 2007 + that will work for 64 Ifs

This is then what you want for your example:

Using Excel 2007

Row\Col	L
1	=IF(F1="Salik Charges","170.110.000.415030.0000.0000.000.000",IF (F1="Hire Charges","170.110.000.415025.0000.0000.000.000",IF (F1="Vehicle Fine","170.110.000.143015.0000.0000.000.000",IF(F1 ="Fuel Charges","170.110.000.415015.0000.0000.000.000","" ))))
2	=IF(F2="Salik Charges","170.110.000.415030.0000.0000.000.000",IF (F2="Hire Charges","170.110.000.415025.0000.0000.000.000",IF (F2="Vehicle Fine","170.110.000.143015.0000.0000.000.000",IF(F2 ="Fuel Charges","170.110.000.415015.0000.0000.000.000","" ))))
3	=IF(F3="Salik Charges","170.110.000.415030.0000.0000.000.000",IF (F3="Hire Charges","170.110.000.415025.0000.0000.000.000",IF (F3="Vehicle Fine","170.110.000.143015.0000.0000.000.000",IF(F3 ="Fuel Charges","170.110.000.415015.0000.0000.000.000","" ))))
4	=IF(F4="Salik Charges","170.110.000.415030.0000.0000.000.000",IF (F4="Hire Charges","170.110.000.415025.0000.0000.000.000",IF (F4="Vehicle Fine","170.110.000.143015.0000.0000.000.000",IF(F4 ="Fuel Charges","170.110.000.415015.0000.0000.000.000","" ))))

_.....................
Here are the results

Row\Col	F	G	H	I	J	K	L
1	Salik Charges						170.110.000.415030.0000.0000.000.000
2	Vehicle Fine						170.110.000.143015.0000.0000.000.000
3	Hire Charges						170.110.000.415025.0000.0000.000.000
4	Fuel Charges						170.110.000.415015.0000.0000.000.000

_.......................

Of course you only need to put the formula in L1 in and then drag down

But: I learnt the hard way it was well worth learning how to use VLookUp, which is much more flexible and neater..

I will show that in the next post ( as those long formulas use up most of the Post space!!!.. )

**DocAElstein** · 03-09-2016, 07:53 PM

_...So this way is better..
_ You make a table anywhere convenient that looks like the what you gave in Post #1

Salik Charges~~~~~~170.110.000.415030.0000.0000.000.000
Hire Charges~~~~~~~170.110.000.415025.0000.0000.000.000
Vehicle Fine~~~~~~~~170.110.000.143015.0000.0000.000.000
Fuel Charges~~~~~~~170.110.000.415015.0000.0000.000.000

_.. then you use the VLookUp, pseudo code

~~~~=~~~(~~~~~Look in column F~~~~,~~~see where that is in your table~~~,~~~~~give out the value in column 2 of that table~~~~~,~~~~~and look for an exact match~~~~~)
_.......................

Here would be one way to do that.
Your table somewhere:

Row\Col	C	D
6	Salik Charges	170.110.000.415030.0000.0000.000.000
7	Hire Charges	170.110.000.415025.0000.0000.000.000
8	Vehicle Fine	170.110.000.143015.0000.0000.000.000
9	Fuel Charges	170.110.000.415015.0000.0000.000.000

_.........................................

Your Formulas

Row\Col	L
1	=VLOOKUP(F1,$C$6:$D$9,2,FALSE)
2	=VLOOKUP(F2,$C$6:$D$9,2,FALSE)
3	=VLOOKUP(F3,$C$6:$D$9,2,FALSE)
4	=VLOOKUP(F4,$C$6:$D$9,2,FALSE)

( Once again you only need to put the formula in L1 in and drag that down.
And Change the dimensions of the table $C$6:$D$9 if you want more stuff in that Table. For example $C$6:$D$10 if you want to add a row to the table.
_....................

Some results:

Row\Col	C	D	E	F	G	H	I	J	K	L
1				Salik Charges						170.110.000.415030.0000.0000.000.000
2				Vehicle Fine						170.110.000.143015.0000.0000.000.000
3				Hire Charges						170.110.000.415025.0000.0000.000.000
4				Fuel Charges						170.110.000.415015.0000.0000.000.000
5
6	Salik Charges	170.110.000.415030.0000.0000.000.000
7	Hire Charges	170.110.000.415025.0000.0000.000.000
8	Vehicle Fine	170.110.000.143015.0000.0000.000.000
9	Fuel Charges	170.110.000.415015.0000.0000.000.000

_...............................................

Alan

**snb** · 03-20-2016, 05:30 PM

Or you might do some analysing/thinking first:

in cell L1:

PHP Code:


="170.110.000." & choose(search(left($F1,1),"FHSV"),"415015","415025","415030","143015")&".0000.0000.000.000"

**DocAElstein** · 03-20-2016, 06:42 PM

Hi snb

Originally Posted by snb

……
in cell L1:

PHP Code:


="170.110.000." & choose(search(left($F1,1),"FHSV"),"415015","415025","415030","143015")&".0000.0000.000.000"

Thanks for that alternative.

Originally Posted by snb

Or you might do some analysing/thinking first:
…….

That “analysing/thinking first” still would not have helped me here, as I do not have the Knowledge and experience of those Choose and Search Functions.
Thanks for giving the benefit of your extra knowledge and experience.

Alan

_...........................................
P.s. I think I get what is going on.......

Using Excel 2007

_Row\^Col	F	J	K	L
1	Salik Charges	3	415030	170.110.000.415030.0000.0000.000.000
2	Vehicle Fine	4	143015	170.110.000.143015.0000.0000.000.000
3	Hire Charges	2	415025	170.110.000.415025.0000.0000.000.000
4	Fuel Charges	1	415015	170.110.000.415015.0000.0000.000.000

Tabelle3

_....................

_Row\^Col	F	G	H	I	J	K	L
1	Salik Charges				=SEARCH(LEFT($F1,1),"FHSV")	=CHOOSE(J1,"415015","415025","415030","143015")	="170.110.000." & CHOOSE(SEARCH(LEFT($F1,1),"FHSV"),"415015","415025","415030","143015")&".0000.0000.000.000"

_............................

Code:

Option Explicit
Sub snbFormula() '   http://www.excelfox.com/forum/f2/multiple-ifs-formula-help-2089/#post9699
Let Range("L1").Value = "=" & """170.110.000."" & choose(search(left($F1,1),""FHSV""),""415015"",""415025"",""415030"",""143015"")&"".0000.0000.000.000"""
'   ="170.110.000." & choose(search(left($F1,1),"FHSV"),"415015","415025","415030","143015")&".0000.0000.000.000"
'   ="170.110.000." & WAHL(SUCHEN(LINKS($F1;1);"FHSV");"415015";"415025";"415030";"143015")&".0000.0000.000.000"
End Sub
' Explanation Maybe:
' = search(left($F1,1),"FHSV") ---- Searching for first letter in FHSV  and returning position along
' =SUCHEN(LINKS($F1;1);"FHSV")
'
' = choose(2,"415015","415025","415030","143015") ---- as example, choose at positon 2 - so here return "415025"
' =WAHL(J1;"415015";"415025";"415030";"143015")
'

**snb** · 03-20-2016, 09:43 PM

@DocE

Why do you use 'Let' ??

Code:

sub M_snb()
  cells(1,12)="170.110.000."& choose(instr("FHSV",left(cells(1,6),1)),"415015","415025","415030","143015")& ".0000.0000.000.000"
end sub

PS.
- most of the time multiple ifs can be replaced by an algorithm that reflects an underlying pattern.
in this case e.g. "4150" & 5*instr(" F HSV",left(cells(1,6),1))
- It might be worthwhile to study systematically every function in Excel, starting with text, information, etc.

**DocAElstein** · 03-20-2016, 10:49 PM

Hi snb

Originally Posted by snb

.......

Code:

sub M_snb()
  cells(1,12)="170.110.000."& choose(instr("FHSV",left(cells(1,6),1)),"415015","415025","415030","143015")& ".0000.0000.000.000"
end sub

........

_ Another one ! !! The ways to do stuff in VBA is never ending!!!

_...............................................

Originally Posted by snb

@DocE
Why do you use 'Let' ??

http://www.excelfox.com/forum/showth...page2#post9459
http://www.excelfox.com/forum/f2/spe....html#post9459

_...............................................

Originally Posted by snb

........
PS.
- most of the time multiple ifs can be replaced by an algorithm that reflects an underlying pattern.
in this case e.g. "4150" & 5*instr(" F HSV",left(cells(1,6),1))
- It might be worthwhile to study systematically every function in Excel, starting with text, information, etc.

_- Thanks for that. Worth bearing in mind.
_ I hope i get time to check it all out. But it is a rather a lot!!!.... _
_ In the meantime it is great to get help from other, more experienced people.

Alan

P.s. I expect for real ( lots of ) data the VLookUp would still be the best, especially if data may be added later.... although you could write a program to produce the formula string.... But as said... the ways to do stuff in VBA is never ending!