Excel function for data across multiple worksheets in same workbook

New Contributor

I am on Office365 for Enterprise Excel Version 2102 build 13801.20960 Windows 10 Pro

my workbook contains import of data from multiple downloads from financial system, I then create lookup tables to combine data into one spreadsheet and then create pivot tables from that data.

I've been asked to add one additional column to the pivot table,  however vlookup isn't working need another option.

basic was =VLOOKUP($B2,OutlineAgree_Y_DRP_63000031!$A$1:$L$455,12,FALSE), problem with this is that the "$B2" is only one of 3 columns that need to match before giving me column 12 from the 2nd spreadsheet within the workbook.  The lookup needs to match three column/row intersects in order to return the correct value.  "B2, D2 and C2" of spreadsheet #1 needs to find same value in tab #2 "OutlineAgree_Y_DRP_63000031!$A$1:$L$455" before returning the value in column 12 of tab #2, otherwise it only returns the first value.

example see attached spreadsheet

 

 

2 Replies

@DevraS 

Excel 365 Pro Plus with Power Query

Lookup with three criteria using PQ Merge()

No formulas, no added merged columns, no VBA macro.

https://www.mediafire.com/file/o9az4zn74o5shqm/10_18_21a.xlsx/file

https://www.mediafire.com/file/62j7styd9te2fn7/10_18_21a.pdf/file

 

@Herbert_Seidenberg   thank you, wish I understood it, but alas I tried and failed, but thank you, it is a bit too complicated for me