lookup number and enter a word depending on row range number was found

[b19upj]

Hi,

I want to look up a number that is in column D from column E, and if it is found in a table on a different tab in column A rows 9-109 put the work 'Expenditure' in the lookup cell in column E, and if the number found from the lookup is in column A rows 121-160, put 'Income' in the lookup cell in column E.

TIA

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ExceLoki]

try this

=IFNA(IFS(COUNTIF(Sheet1!$A$9:$A$108,D1)=1,"Exp",COUNTIF(Sheet1!$A$111:$A$160,D1)=1,"Inc"),"")

 

[Fluff]

There is no need to use the IFNA if you set the final argument in the IFS to true

=IFS(COUNTIF(Sheet1!$A$9:$A$108,D1)=1,"Exp",COUNTIF(Sheet1!$A$111:$A$160,D1)=1,"Inc",1,"")