Hello everyone,
I have 3 sheets that holds values for my stocks, funds and forexs.
I’m trying to create a macro that will use the type at column A and refer to the corresponding sheet to look
for the requested value. This is the macro I’ve created:
=SWITCH( A2, "FUND",VLOOKUP(B2,$Fund.A$3:C$35,3,0), "STOCK",VLOOKUP(B2,$Stock.A$3:C$35,3,0), "FX",VLOOKUP(B2,$Fx.A$3:C$35,3,0))
This macro above is returning the correct values only for FUND
rows. All other rows gets #N/A
This one only works for STOCK
rows. All others gets #N/A
=SWITCH( A3,"STOCK",VLOOKUP(B3,$Stock.A$3:C$35,3,0), "FUND",VLOOKUP(B3,$Fund.A$3:C$35,3,0), "FX",VLOOKUP(B3,$Fx.A$3:C$35,3,0))
This one only works for FX
rows. All others gets #N/A
=SWITCH( A3,"FX",VLOOKUP(B3,$Fx.A$3:C$35,3,0),"STOCK",VLOOKUP(B3,$Stock.A$3:C$35,3,0), "FUND",VLOOKUP(B3,$Fund.A$3:C$35,3,0))
But when I write them sperately they work fine.
=VLOOKUP(B5,$Fx.A$3:C$35,3,0)
=VLOOKUP(B8,$Stock.A$3:C$35,3,0)
=VLOOKUP(B18,$Fund.A$3:C$35,3,0)
As you can see from the image below, the last VLOOKUP retrieves the correct value but it won’t resolve as the SWITCH statement’s result value.
Also I’ve noticed when I replaced the VLOOKUP with some string value, It works fine. So this means it evaluates the statement and yet it doesn’t return the value to the SWITCH when using VLOOKUP.
For example this one prints THIS IS A STOCK when A9 is STOCK
SWITCH( A9,"FX",VLOOKUP(B9,$Fx.A$3:C$35,3,0),"STOCK","THIS IS A STOCK", "FUND",VLOOKUP(B9,$Fund.A$3:C$35,3,0))
Am i doing something wrong in here?