VLOOKUP - Tricky problem, help please!

[David]

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]

=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

 

[Stefi]

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:

 

[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]

Sorry Dave for disturbing you with such a silly error of mine! That was as
simple!
Thanks,
Stefi


„Dave Peterson†ezt írta:

 

[David]

Thanks everyone!

 

[Stefi]

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

„David†ezt írta: