Edit/improve a formula

[Dan_K_2014]

Hell all,
I have a formula:
=IFERROR(IF(J4="","Revenue")&(VLOOKUP(J4,List!G2:I49,3)),"")
Vlookup is looking for a Name based on J4 code. I want to have a name in cell C7 "Revenue" if J4 is empty and Vlookup does not bring any name.
Thank you.

 

[Haz]

Assume J4 is empty. IF returns "Revenue" and VLOOKUP returns an ERROR, concatenate "Revenue"&ERROR gives an ERROR, IFERROR returns "".

Fix:
=IFERROR(IF(J4="","Revenue",VLOOKUP(J4,List!G2:I49,3)),"")
When J4 is empty IF returns "Revenue", when it's not empty it searches the list.

 

[Peter Bartholomew]

There are all sorts of issues here and a formula 'improvement' can only be judged in the context of the actual situation.

You have used the default (FALSE) for the 4th parameter of VLOOKUP.
Is your list of codes actually sorted in ascending order?
Do you have validation in place to ensure the code in J4 is taken from the list?
If not, what do you want to return where
(i) code < 1st element of list (error trapped to give "")
(ii) code is between two elements of the list (name from preceding code)
(iii) code > final element of the list? (name from final code)
(iv) code = "" (return "Revenue")
It may be that a second VLOOKUP is needed as a test
VLOOKUP(J4,List!G2:I49,1)=J4

 

[Dan_K_2014]

Thank you everybody for help. Haz's- it is working.