CODEWITHSUNDEEP
excelexcel-formula
Night Monger
Night Monger

Reputation: 780

Excel Cell value based on VLookup

I have my sheet like below
enter image description here

I need to automatically fille the ones in Red based on the look up value created in another sheet of same workbook. the lookup list is as below:
enter image description here

if it is under partner - it should show as partner, and if there is no value in lookup list , it can return "unknown" as default.

Any idea on how to get this done?

Upvotes: 0

Views: 59

Answers (1)

Xeozim
Xeozim

Reputation: 73

You can achieve this using a formula in your Company Type column.

Assuming the sheet containing the lookup data is called "data", and partners are listed in column A and competitors in column B. If you're trying to get the company type for the company in cell A2:

=IF(COUNTIF('Data'!$A:$A,A2)>0,"Partner",IF(COUNTIF('Data'!$B:$B,A2)>0,"Competitor","Unknown"))

Briefly:

COUNTIF('Data'!$A:$A,A2)>0

Counts all occurences that match the company name in A2 in the list of partner companys in column A of the Data sheet. If there is more than 0, this code block will return TRUE. This is then used to guide if statements determining the company type.

Using this example company data, I produced the lookup results shown. Hopefully this is what you're after.

Regards,

Xeozim

Edit: switched to COUNTIF for a simpler check

Upvotes: 1

Related Questions