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
1
2	Type of income	Country of source of income	USD								Lookup table
3	Royalty fee	Botswana	5000		=IF(A2="Royalty Fee", "Foreign","Local")						Royalty fee	Botswana
4	Client	Swaziland	10000		=IF(A3="Client", "Foreign","Local")						Client	Swaziland
5	Franchise fee	Lesotho	4500		=IF(A4="Franchise Fee", "Foreign","Local")						Franchise fee	Lesotho
6	Interest	Uganda	3000		=IF(A5="Interest", "Foreign","Local")						Interest	Uganda
7	IT Change	Ghana	1000		=IF(A6="IT Change", "Local","Foreign")						IT Change	Ghana
8	Commission	Zambia	500		=IF(A7="Commission", "Local","Foreign")						Commission	Zambia
9					But / OR
10					=IF(OR(A7&B7=$L$7&$M$7,A8&B8=$L$8&$M$8,A8&B8=$L$8&$M$3),"Foreign","Local")
11
12

<tbody>
</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")