IF Statement

[Deonvg]

Hi all,

I have the following table with data and it references against a "lookup" table (column K:L).

Somehow I need to combine a IF formula to do all the of the below, have tried doing it with "OR & AND", but just don't seem to be getting it correct. Pending on the combination of Country in "lookup table" & column A it needs to bring back Foreign

Your help would be appreciated.

Thanks in advance.(sorry can't install proper paste tool for excel as i'm on my work laptop)

	A	B	C	D	E	F	G	H	I	J	K	L	M
Lookup table
Royalty fee	=IF(A2="Royalty Fee", "Foreign","Local")	Royalty fee
Client	=IF(A3="Client", "Foreign","Local")	Client
Franchise fee	=IF(A4="Franchise Fee", "Foreign","Local")	Franchise fee
Interest	=IF(A5="Interest", "Foreign","Local")	Interest
=IF(A6="IT Change", "Local","Foreign")
=IF(A7="Commission", "Local","Foreign")
But / OR
=IF(OR(A7&B7=$L$7&$M$7,A8&B8=$L$8&$M$8,A8&B8=$L$8&$M$3),"Foreign","Local")

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Type of income[/TD]
[TD="align: center"]Country of source of income[/TD]
[TD="align: center"]USD[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #FFC000"]Botswana[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFC000"]Botswana[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #FFC000"]Swaziland[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFC000"]Swaziland[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #FFC000"]Lesotho[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFC000"]Lesotho[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #FFC000"]Uganda[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFC000"]Uganda[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFC000"]IT Change[/TD]
[TD="bgcolor: #FFC000"]Ghana[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFC000"]IT Change[/TD]
[TD="bgcolor: #FFC000"]Ghana[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFC000"]Commission[/TD]
[TD="bgcolor: #FFC000"]Zambia[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFC000"]Commission[/TD]
[TD="bgcolor: #FFC000"]Zambia[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[JackDanIce]

Instead of a long nested IF statement, you could set up a VLOOKUP table and use that, which would be easier

 

[Deonvg]

Instead of a long nested IF statement, you could set up a VLOOKUP table and use that, which would be easier

I would still have to create some kind of unique identifier to bring in the variable of last 2 income types. If any of those 2 income types match with any of the countries in column "L" I want a value of "Foreign" to be returned, but if the income type and country doesn't match it should be local.

If the income type for the last 2 doesn't match with a certain country it should be local, thus the amount of unique identifiers would increase quite drastically

Income types form A3:A6 should immediately give me Foreign.

 

[thisoldman]

Try this.
=IF(((A3="IT Change")+(A3="Commission"))*ISNUMBER(MATCH(B3,$L$3:$L$8,0)), "Local", "Foreign")

Or this, using AND and OR.
=IF(AND(OR(A3="IT Change",A3="Commission"),ISNUMBER(MATCH(B3,$L$3:$L$8,0))), "Local", "Foreign")