Forum Discussion

Kim Robin Hicks's avatar
Kim Robin Hicks
Copper Contributor
Nov 16, 2017

Formula Help for Vlookup and IF

I have a worksheet with three tabs.  Tab A is my working tab.  I need to perform a VLOOKUP to find data in a cell on Tab B.  If there is no match on Tab B, I need to produce the value from Tab C.  I also need to multiple any cell amount by 1000.

 

I wrote:

 

=IF(((VLOOKUP(A71,TabB,8,FALSE)*1000)>0),((VLOOKUP(A71,TabB,8,FALSE)*1000)),((VLOOKUP(A72,TabC,10,FALSE)*1000)))

 

I know I could use "" but I chose not to.

 

I get the correct value if there is a match in Tab B, but if there is not a match, I just get N/A.  It's not looking at Tab C at all.

 

Thanks for your help.

  • Hi Kim,

     

    You shall check on error, like

    =IF(ISNA(VLOOKUP on TabB), VLOOKUP on TabC, VLOOKUP on TabB) 

     

  • Hi Kim,

     

    You shall check on error, like

    =IF(ISNA(VLOOKUP on TabB), VLOOKUP on TabC, VLOOKUP on TabB) 

     

Resources