Formula help VLOOKUP? MATCH? INDEX>

%3CLINGO-SUB%20id%3D%22lingo-sub-1614397%22%20slang%3D%22en-US%22%3EFormula%20help%20VLOOKUP%3F%20MATCH%3F%20INDEX%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614397%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20There%2C%3C%2FP%3E%3CP%3EI've%20completed%20a%20data%20query%20and%20have%20a%20long%20list%20in%20Sheet%20A%20with%20three%20columns%3A%3C%2FP%3E%3CP%3EColumn%20A%20are%20dates%3C%2FP%3E%3CP%3EColumn%20B%20are%20the%20same%205%20items%20repeated%20every%20five%20rows%2C%20let's%20say%3C%2FP%3E%3CP%3ERevenues%3C%2FP%3E%3CP%3ECoGS%3C%2FP%3E%3CP%3EPayroll%20Expense%3C%2FP%3E%3CP%3EAdmin%20Expense%3C%2FP%3E%3CP%3ENet%20Income%3C%2FP%3E%3CP%3EColumn%20C%20are%20the%20values%3C%2FP%3E%3CP%3ESheet%20B%20has%20the%20dates%20across%20the%20top%20row%20and%20the%20items%20in%20column%20B%20in%20the%20first%20column%3C%2FP%3E%3CP%3EI%20want%20to%20search%20the%20data%20in%20Sheet%20A%20and%20fill%20in%20the%20values%20in%20Sheet%20B%20to%20match%20the%20date%20in%20the%20top%20row%20and%20the%20label%20in%20the%20first%20column.%3C%2FP%3E%3CP%3ECan%20anyone%20suggest%20a%20formula%20for%20that%3F%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1614397%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614421%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3F%20MATCH%3F%20INDEX%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614421%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F766223%22%20target%3D%22_blank%22%3E%40karenynp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B2%20on%20Sheet%20B%3A%3C%2FP%3E%0A%3CP%3E%3DSUMIFS('Sheet%20A'!%24C%242%3A%24C%2421%2C'Sheet%20A'!%24A%242%3A%24A%2421%2CB%241%2C'Sheet%20A'!%24B%242%3A%24B%2421%2C%24A2)%3C%2FP%3E%0A%3CP%3EChange%20Sheet%20A%20to%20the%20actual%20name%20of%20the%20first%20sheet%2C%20and%20adjust%20the%20ranges%20as%20needed.%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%2C%20then%20down%2C%20or%20vice%20versa.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614475%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3F%20MATCH%3F%20INDEX%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614475%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20so%20simple!%26nbsp%3B%20Thank%20you.%26nbsp%3B%20Someone%20kept%20telling%20me%20to%20use%20VLOOKUP%20and%20I%20was%20struggling.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614623%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3F%20MATCH%3F%20INDEX%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614623%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bsorry%20another%20problem.%26nbsp%3B%20The%20data%20I%20am%20referring%20to%20in%20Sheet%20A%20is%20in%20a%20data%20table.%26nbsp%3B%20When%20I%20point%20to%20the%20table%20in%20Sheet%20A%2C%20I%20get%20the%20correct%20values%2C%20but%20the%20columns%20are%20relative%20references%20so%20I%20cannot%20copy%20them%20across%20in%20Sheet%20B.%26nbsp%3B%20%26nbsp%3BIf%20I%20try%20to%20create%20a%20reference%20using%20'Sheet%20A'!%24b%242%3A%24b%24109%2C%20the%20sumif%20does%20not%20find%20the%20value.%26nbsp%3B%20This%20is%20my%20first%20time%20using%20data%20tables%20outside%20of%20the%20classroom%20so%20I%20am%20wondering%20if%20there%20is%20some%20trick%20to%20referring%20to%20table%20references%20absolutely.%26nbsp%3B%20Or%20do%20I%20need%20to%20take%20the%20data%20out%20of%20table%20format%3F%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614669%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3F%20MATCH%3F%20INDEX%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614669%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F766223%22%20target%3D%22_blank%22%3E%40karenynp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20does%20indeed%20not%20work%20correctly%20if%20you%20use%20structured%20table%20references%2C%20but%20as%20far%20as%20I%20can%20tell%20it%20should%20work%20OK%20if%20you%20use%20range%20references%20even%20if%20the%20data%20on%20Sheet%20A%20are%20in%20a%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20sample%20workbook%20without%20sensitive%2Fproprietary%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614717%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3F%20MATCH%3F%20INDEX%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614717%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bhere%20is%20a%20sample%26nbsp%3B%20-%20Sheet%202%2C%20Column%20Z%20has%20an%20example%20of%20what%20I%20want%20to%20see%20and%20be%20able%20to%20copy%20across%20to%20other%20dates.%26nbsp%3B%20Sheet%20A%20currently%20has%20just%20one%20year%20and%20location%20but%20I%20think%20that%20is%20enough%20to%20solve%20the%20problem%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614734%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3F%20MATCH%3F%20INDEX%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614734%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F766223%22%20target%3D%22_blank%22%3E%40karenynp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20should%20work%20in%20Z3%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS(_2019__7%5B%5BColumn2%5D%3A%5BColumn2%5D%5D%2C_2019__7%5B%5BColumn1%5D%3A%5BColumn1%5D%5D%2C%24A3%2C_2019__7%5B%5BColumn3%5D%3A%5BColumn3%5D%5D%2CZ%241)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20to%20the%20right.%20See%20the%20attached%20version.%3C%2FP%3E%0A%3CP%3EThe%20idea%20is%20from%20%3CA%20href%3D%22https%3A%2F%2Fwww.excelcampus.com%2Ftips-shortcuts%2Fabsolute-formula-references-excel-structured-table%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAbsolute%20Structured%20References%20in%20Excel%20Table%20Formulas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614739%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3F%20MATCH%3F%20INDEX%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614739%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bthanks%20again%20for%20your%20help!!%26nbsp%3B%20It%20works%20great!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615217%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3F%20MATCH%3F%20INDEX%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F766223%22%20target%3D%22_blank%22%3E%40karenynp%3C%2FA%3E%26nbsp%3BWhy%20not%20use%20a%20pivot%20table.%20Your%20data%20is%20perfectly%20structured%20for%20it.%20No%20need%20for%20formulae%20and%20you%20can%20easily%20adopt%20the%20tables%20when%20you%20enter%20data%20for%20more%20than%20one%20year%20and%2For%20location.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi There,

I've completed a data query and have a long list in Sheet A with three columns:

Column A are dates

Column B are the same 5 items repeated every five rows, let's say

Revenues

CoGS

Payroll Expense

Admin Expense

Net Income

Column C are the values

Sheet B has the dates across the top row and the items in column B in the first column

I want to search the data in Sheet A and fill in the values in Sheet B to match the date in the top row and the label in the first column.

Can anyone suggest a formula for that?

Thank you

 

8 Replies
Highlighted

@karenynp 

In B2 on Sheet B:

=SUMIFS('Sheet A'!$C$2:$C$21,'Sheet A'!$A$2:$A$21,B$1,'Sheet A'!$B$2:$B$21,$A2)

Change Sheet A to the actual name of the first sheet, and adjust the ranges as needed.

Fill to the right, then down, or vice versa.

Highlighted

@Hans Vogelaar  so simple!  Thank you.  Someone kept telling me to use VLOOKUP and I was struggling.

Highlighted

@Hans Vogelaar sorry another problem.  The data I am referring to in Sheet A is in a data table.  When I point to the table in Sheet A, I get the correct values, but the columns are relative references so I cannot copy them across in Sheet B.   If I try to create a reference using 'Sheet A'!$b$2:$b$109, the sumif does not find the value.  This is my first time using data tables outside of the classroom so I am wondering if there is some trick to referring to table references absolutely.  Or do I need to take the data out of table format?   

Highlighted

@karenynp 

The formula does indeed not work correctly if you use structured table references, but as far as I can tell it should work OK if you use range references even if the data on Sheet A are in a table.

 

Could you attach a sample workbook without sensitive/proprietary data?

Highlighted

@Hans Vogelaar here is a sample  - Sheet 2, Column Z has an example of what I want to see and be able to copy across to other dates.  Sheet A currently has just one year and location but I think that is enough to solve the problem with.

 

Highlighted

@karenynp 

This should work in Z3:

 

=SUMIFS(_2019__7[[Column2]:[Column2]],_2019__7[[Column1]:[Column1]],$A3,_2019__7[[Column3]:[Column3]],Z$1)

 

This can be filled to the right. See the attached version.

The idea is from Absolute Structured References in Excel Table Formulas 

Highlighted

@Hans Vogelaar thanks again for your help!!  It works great!

 

Highlighted

@karenynp Why not use a pivot table. Your data is perfectly structured for it. No need for formulae and you can easily adopt the tables when you enter data for more than one year and/or location.

 

See attached.