EXCEL 2007

W.E.B
W.E.B used Ask the Experts™
on
Hello,
not sure what I'm doing wrong,
Here is the requirement, (I'm trying to copmare sales between periods of 2 years)-
--Column H--
Find Client from Column K in Column C, If the client is found, results ,
(SALES in column M - Sales in column E) --  for the client.
I tried to use =LOOKUP(K3,$C$3:$C$206,$E$3:$E$206) to only get the value of sales (without even the formula), but the results are coming wrong.
Any help is appreciated.


Sales-Comparison.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
You should use INDEX/MATCH, or if you prefer, VLOOKUP, instead.

For example, put this in H3, and copy down as needed:

=INDEX(E:E,MATCH(K3,C:C,0))

As a VLOOKUP:

=VLOOKUP(K3,C:E,3,FALSE)

For more about VLOOKUP: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html

INDEX/MATCH is usually more efficient than VLOOKUP.

Both return a #N/A error if there are no matches found; use the IFERROR function if desired to show a replacement value:

=IFERROR(INDEX(E:E,MATCH(K3,C:C,0)),"no match!")

=IFERROR(VLOOKUP(K3,C:E,3,FALSE),"no match!")

Author

Commented:
HI Mathew,
=INDEX(E:E,MATCH(K3,C:C,0))
this works great, how do you add the formula, to get return as
difference, (SALES in column M - Sales in column E)

thanks,
Top Expert 2010

Commented:
Try this:

=M3-INDEX(E:E,MATCH(K3,C:C,0))

That will return a #N/A error if K3 has no match in Col C.  If you want <Sales in Col E> treated as zero if there is no match:

=M3-IFERROR(INDEX(E:E,MATCH(K3,C:C,0)),0)

Patrick
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Perfect,
I will be using both for different reports,
thanks.

Author

Commented:
sorry, accepted my own , instead of Patrick.

Author

Commented:
Excellent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial