|
VLOOKUP - Tricky problem, help please!
Hello
I have the following formula: =IF(ISERROR(VLOOKUP($A150,'FURL Data'!$C:$G,3,FALSE)),0,(VLOOKUP ($A150,'FURL Data'!$C:$G,3,FALSE))) This was working fine, but for some reason my source data has changed so that the figures I need now span two separate rows, where they were one before. So the above is no longer working as it only finds the first row. Is there an easy way to amend the above, so it finds both rows and adds them together? Would be grateful for any assistance! |
VLOOKUP - Tricky problem, help please!
=match() will return the row of the first match:
=match($a150,'furl data'!$c:$c,0) will be the number of the row with that match. If you wanted to bring back information from that row, you could use: =index('furl data'!$e:$e,match($a150,'furl data'!$c:$c,0)) So you could add a number (or subtract) to offset from that first row's match: =index('furl data'!$e:$e,1+match($a150,'furl data'!$c:$c,0)) You may want the same check to see if there is a match: =if(isna(match($a150,'furl data'!$c:$c,0)),0,.... Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble David wrote: Hello I have the following formula: =IF(ISERROR(VLOOKUP($A150,'FURL Data'!$C:$G,3,FALSE)),0,(VLOOKUP ($A150,'FURL Data'!$C:$G,3,FALSE))) This was working fine, but for some reason my source data has changed so that the figures I need now span two separate rows, where they were one before. So the above is no longer working as it only finds the first row. Is there an easy way to amend the above, so it finds both rows and adds them together? Would be grateful for any assistance! -- Dave Peterson |
VLOOKUP - Tricky problem, help please!
Hi Dave,
I tried to solve David problem, and I thought that =SUM(OFFSET(INDIRECT(ADDRESS(MATCH($A150,'Furl Data'!C:C,0),5,4,1,"Munka1")),0,0,2,1)) should work but it doesn't. I couldn't figure out why. Could you explain me the reason, please! Regards, Stefi Dave Peterson ezt *rta: =match() will return the row of the first match: =match($a150,'furl data'!$c:$c,0) will be the number of the row with that match. If you wanted to bring back information from that row, you could use: =index('furl data'!$e:$e,match($a150,'furl data'!$c:$c,0)) So you could add a number (or subtract) to offset from that first row's match: =index('furl data'!$e:$e,1+match($a150,'furl data'!$c:$c,0)) You may want the same check to see if there is a match: =if(isna(match($a150,'furl data'!$c:$c,0)),0,.... Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble David wrote: Hello I have the following formula: =IF(ISERROR(VLOOKUP($A150,'FURL Data'!$C:$G,3,FALSE)),0,(VLOOKUP ($A150,'FURL Data'!$C:$G,3,FALSE))) This was working fine, but for some reason my source data has changed so that the figures I need now span two separate rows, where they were one before. So the above is no longer working as it only finds the first row. Is there an easy way to amend the above, so it finds both rows and adds them together? Would be grateful for any assistance! -- Dave Peterson |
VLOOKUP - Tricky problem, help please!
Maybe it's as simple as changing your "munka1" test worksheet to "furl data"????
After that it worked ok for me--if there was a match. Stefi wrote: Hi Dave, I tried to solve David problem, and I thought that =SUM(OFFSET(INDIRECT(ADDRESS(MATCH($A150,'Furl Data'!C:C,0),5,4,1,"Munka1")),0,0,2,1)) should work but it doesn't. I couldn't figure out why. Could you explain me the reason, please! Regards, Stefi Dave Peterson ezt *rta: =match() will return the row of the first match: =match($a150,'furl data'!$c:$c,0) will be the number of the row with that match. If you wanted to bring back information from that row, you could use: =index('furl data'!$e:$e,match($a150,'furl data'!$c:$c,0)) So you could add a number (or subtract) to offset from that first row's match: =index('furl data'!$e:$e,1+match($a150,'furl data'!$c:$c,0)) You may want the same check to see if there is a match: =if(isna(match($a150,'furl data'!$c:$c,0)),0,.... Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble David wrote: Hello I have the following formula: =IF(ISERROR(VLOOKUP($A150,'FURL Data'!$C:$G,3,FALSE)),0,(VLOOKUP ($A150,'FURL Data'!$C:$G,3,FALSE))) This was working fine, but for some reason my source data has changed so that the figures I need now span two separate rows, where they were one before. So the above is no longer working as it only finds the first row. Is there an easy way to amend the above, so it finds both rows and adds them together? Would be grateful for any assistance! -- Dave Peterson -- Dave Peterson |
VLOOKUP - Tricky problem, help please!
Sorry Dave for disturbing you with such a silly error of mine! That was as
simple! Thanks, Stefi Dave Peterson ezt *rta: Maybe it's as simple as changing your "munka1" test worksheet to "furl data"???? After that it worked ok for me--if there was a match. Stefi wrote: Hi Dave, I tried to solve David problem, and I thought that =SUM(OFFSET(INDIRECT(ADDRESS(MATCH($A150,'Furl Data'!C:C,0),5,4,1,"Munka1")),0,0,2,1)) should work but it doesn't. I couldn't figure out why. Could you explain me the reason, please! Regards, Stefi âžDave Petersonâ ezt Ã*rta: =match() will return the row of the first match: =match($a150,'furl data'!$c:$c,0) will be the number of the row with that match. If you wanted to bring back information from that row, you could use: =index('furl data'!$e:$e,match($a150,'furl data'!$c:$c,0)) So you could add a number (or subtract) to offset from that first row's match: =index('furl data'!$e:$e,1+match($a150,'furl data'!$c:$c,0)) You may want the same check to see if there is a match: =if(isna(match($a150,'furl data'!$c:$c,0)),0,.... Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble David wrote: Hello I have the following formula: =IF(ISERROR(VLOOKUP($A150,'FURL Data'!$C:$G,3,FALSE)),0,(VLOOKUP ($A150,'FURL Data'!$C:$G,3,FALSE))) This was working fine, but for some reason my source data has changed so that the figures I need now span two separate rows, where they were one before. So the above is no longer working as it only finds the first row. Is there an easy way to amend the above, so it finds both rows and adds them together? Would be grateful for any assistance! -- Dave Peterson -- Dave Peterson |
VLOOKUP - Tricky problem, help please!
Thanks everyone!
|
VLOOKUP - Tricky problem, help please!
Mostly to Dave!
You are welcome! Thanks for the feedback! Stefi David ezt *rta: Thanks everyone! |
| All times are GMT +1. The time now is 11:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com