Multiple IF's Formula Help!!

[msiyab]

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]

The question is not clear at least for me :( Can you please elaborate in detail ?

[msiyab]

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]

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
1Salik Charges170.110.000.415030.0000.0000.000.000

2Vehicle Fine170.110.000.143015.0000.0000.000.000

3Hire Charges170.110.000.415025.0000.0000.000.000

4Fuel Charges170.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]

_...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
1Salik Charges170.110.000.415030.0000.0000.000.000

2Vehicle Fine170.110.000.143015.0000.0000.000.000

3Hire Charges170.110.000.415025.0000.0000.000.000

4Fuel Charges170.110.000.415015.0000.0000.000.000

5

6Salik Charges170.110.000.415030.0000.0000.000.000

7Hire Charges170.110.000.415025.0000.0000.000.000

8Vehicle Fine170.110.000.143015.0000.0000.000.000

9Fuel Charges170.110.000.415015.0000.0000.000.000

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


Alan

[snb]

Or you might do some analysing/thinking first:

in cell L1:


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

[DocAElstein]

Hi snb

……
in cell L1:

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

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


G


H


I


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"



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


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]

@DocE

Why do you use 'Let' ??


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]

Hi snb

.......

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!!! :)
_...............................................

@DocE
Why do you use 'Let' ??

http://www.excelfox.com/forum/showthread.php/2042-Special-concatenation/page2#post9459
http://www.excelfox.com/forum/f2/special-concatenation-2042/index2.html#post9459
:rolleyes:
_...............................................


........
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!