May 26 2020 09:59 AM
I'm trying to create a vlookup formula that uses a cell reference that contains the name of a cell range.
Cell A3 contains the name of a cell range "MOVIL". In cell D3 I entered a formula that looks for a value, but that value depends of the range named in A3 (there are 3 other range names). The Vlookup formula in D3 is: =IFNA(IFERROR(ROUND($C3*VLOOKUP($B3,"A3",4,FALSE),2),VLOOKUP($B3,"A3",4,FALSE)),""). The problem is that vlookup does not understand that A3 contains the name of a range. I tried also without "".
Can you help me fix this formula??
May 26 2020 10:34 AM
May 26 2020 10:36 AM
May 26 2020 10:46 AM
Hi and thanks,
but when I changed using INDIRECT a #REF! error resulted:
I'm including the screenshot of the Name Manager.
May 26 2020 10:48 AM
May 26 2020 10:56 AM
A detail that I didn't mentioned before is that A3 value was filled using a drop down list that I defined using the data validation window.
May 26 2020 11:04 AM
Solution@CarlMatDi , your named range is referring to only one column A3 to A500 (=>excel knows about only one column), but you are mentioning 4 in the Vlookup formula , so excel cannot recognize the 4th column, that is why it is returni ng #REF error
For Vlookup to return the value on 4th column starting from column A, your named range should be something like =Movil!$A$3:$D$500
May 26 2020 11:22 AM
May 26 2020 11:04 AM
Solution@CarlMatDi , your named range is referring to only one column A3 to A500 (=>excel knows about only one column), but you are mentioning 4 in the Vlookup formula , so excel cannot recognize the 4th column, that is why it is returni ng #REF error
For Vlookup to return the value on 4th column starting from column A, your named range should be something like =Movil!$A$3:$D$500