Read data from anotherworkbook/tab

[humanoid769]

Hi

I am trying to setup an =IF statement to lookup a value from a certain tab1 within a workbook based upon a field. So if B2=Money Map 1 then go to mony map 1 tab if it is Money Map 2 then read from money map 2 tab if anything else then leave it 0.

I currently have:

=IF(B2="Money Map 1",VLOOKUP(A4,'H:\Downloads\[1b. Pro Tactics BSP Order.xlsm]Money Map 1'!$A$611:$AB$33837,24,FALSE),IF(B2="Money Map 2",VLOOKUP(A4,'H:\Downloads\[1b. Pro Tactics BSP Order.xlsm]Money Map 2'!$A$611:$AB$33837,24,FALSE),0))

But it always returns a 0

Any advice please

Thanks
Tony

 

[MCLIFTO8]

Hi Tony,
I like to debug my formulas step by step using a combination of breaking the formula up into pieces, replacing parts of the formula with dummy values and using the "evaluate Formula" function in "Formulas" tab.

Here's the formula you were requesting. I haven't debugged your Vlookup, it's a waste of time. Just use X lookup.
You could further modify your LET function to include the path as a variable if you wished.

=LET(sheetName,B2,XLOOKUP(A4,INDIRECT("'H:\Downloads\[1b. Pro Tactics BSP Order.xlsm]"&sheetName&"'!$A611:$AB$33837"),INDIRECT("'H:\Downloads\[1b. Pro Tactics BSP Order.xlsm]"&sheetName&"'!$X1:$X33837"),"not found",0))

If you're finding the above isn't a direct drop in replacement, this is the formula I tested on a dummy worksheet.

=LET(sheetName,B2,XLOOKUP(A4,INDIRECT("'"&sheetName&"'!$A1:$A10"),INDIRECT("'"&sheetName&"'!$B1:$B10"),"not found",0))