Trying to write a vlookup formula to look-up the date of the column instead of the row number, so when I copy the formula, I don't have to continue to change the ROW number inside my formula for my 52 week spreadsheet.
=IF(ISERROR(VLOOKUP($A2,'CURRENT SALES'!$A$1:$BJ$190,4,FALSE)*$D2),0,VLOOKUP($A2,'CURRENT SALES'!$A$1:$BJ$190,4,FALSE)*$D2)
Tried all kinds of MATCH, INDEX, LOOKUP combinations, just can't seem to find the right combination.
Thanks for any help you can provide.
Hello Prodschdler there is #Value Errors on the second sheet
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Try this in G2 =INDEX('CURRENT SALES'!$C$2:$BJ$23,MATCH($A2,'CURRENT SALES'!$A$2:$A$23,0),MATCH(G$1,'CURRENT SALES'!$D$1:$BJ$1,0))*$D$2
Drag across and Down in sheet 1
Or =iferror(INDEX('CURRENT SALES'!$C$2:$BJ$23,MATCH($A2,'CURRENT SALES'!$A$2:$A$23,0),MATCH(G$1,'CURRENT SALES'!$D$1:$BJ$1,0)),0)*$D$2
Last edited by hemesh; 10-18-2013 at 11:13 AM. Reason: multiplication
change the red one
with this
=IF(ISERROR(VLOOKUP($A2,'CURRENT SALES'!$A$1:$BJ$23,4,FALSE)*$D2),0,VLOOKUP($A2,'CURRENT SALES'!$A$1:$BJ$23,4,FALSE)*$D2)
so it will be like this:=MATCH(G$1,'CURRENT SALES'!$1:$1,0)
=VLOOKUP($A2,'CURRENT SALES'!$A$1:$BJ$23,MATCH(G$1,'CURRENT SALES'!$1:$1,0),FALSE)*$D2
Click (*) if you received helpful response.
Regards,
David
hemesh, SDCh,
Thanks for the solutions. Both work fine. What does $1:$1 represent in the MATCH statement, is that just excel shorthand for top ROW?
Thank again!
That is "locking" row 1 in place in that formula so that when you copy down, the reference wont change - called absoluting...
$ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...
$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across
A slight veriation is when you use just a row (or column) as the range, then you just absolute as $1:$1
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
FDinnins,
Your last statement was what I was wondering. I've never seen it written like that before, thought you always had to have the COLUMN letter as well.
Good info, thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks