Forum Discussion
Excel Formula Question
Hello,
I am a very basic excel user, so please excuse any wrong terminology.
I currently have a wage cost spread sheet built that pulls data from a few different excel spreadsheets. My issue is we are switching payroll programs and when we generate a department rollup excel sheet in our new program, the headers are always different. Previously, we used the vlookup formula that uses a set cell reference. Since we are unable to do this with the new program, is there an equation that can use what's typed into the cell(like "total") as a reference point vs cell name(like "c2")? That way, no matter how the excel sheet gets generated from the program , the formula will search the sheet for a given reference point and then pull date from there.
Thank you!
James
2 Replies
Hello,
maybe you can use Index/Match instead of Vlookup? With Match you can look for the column that has the word "total" in a specific row. In this example, you can see the Vlookup formula, which is fixed to look in column E, and also the Index/Match formula, which can find the total, no matter which column it is in.
=VLOOKUP("b",$A$2:$F$6,5,FALSE) =INDEX($A$2:$F$6,MATCH("b",$A$2:$A$6,0),MATCH("total",$A$2:$F$2,0)) Hi James,
You may use Excel Names https://support.office.com/en-us/article/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64 or Excel Tables https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c (above are support article are just introduction)
