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!

=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

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

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

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

Thanks everyone!

Mostly to Dave!
You are welcome! Thanks for the feedback!
Stefi

€žDavid€� ezt Ã*rta:

Thanks everyone!