Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 4
There are 1 users currently browsing forums.
|
 |
07-02-2009, 10:51 AM
|
|
Valued Forum Contributor
|
|
Join Date: 23 Mar 2008
Location: Tennessee
MS Office Version:Office 2007
Posts: 461
|
|
|
IF and VLOOKUP formula.
Please Register to Remove these Ads
How's it going, smart people?
I'm having problems with a formula that I want to return a result IF a certain value is found in another cell. I've attached a sample workbook, but here's a description:
On the "Inventory" worksheet, in column H, I want to lookup the quantity in column B from the "Warehouse Response" worksheet. If the Part # in column E of the response sheet is NI1088106, I want THAT value to show under the NI1088106 part number, and I want the quantities for the NI1089106 to show under that part number. Ideally, the result of the formula would show a "0" if no data is found.
Here's the formula I tried beside the NI1088106 data, but it's not giving the desired results:
=IF('Warehouse Response'!E:E="NI1088106", VLOOKUP(LEFT(A3,3),data,2,0))
Thanks in advance for any help you can offer.
Last edited by hutch@edge.net; 07-02-2009 at 11:22 AM.
|
07-02-2009, 11:04 AM
|
 |
Forum Guru
|
|
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,616
|
|
|
Re: IF and VLOOKUP formula.
With your current setup you would really be obliged to go with a SUMPRODUCT:
Code:
H3:
=SUMPRODUCT(--('Warehouse Response'!$A$12:$A$34=LEFT(Inventory!$A3,3)),--("("&'Warehouse Response'!$E$12:$E$34&")"=$B3),'Warehouse Response'!$B$12:$B$34)
Lots of SUMPRODUCTs are bad news IMO and best avoided.
You could use a LOOKUP(2,1/ approach but that would be not be quick and would necessitate a handler for no results.
Your best bet IMO would be to create a concatenation on Warehouse Response such that:
Code:
H12: =$A12&":("&$E12&")"
copied down
You can then use a standard SUMIF on your summary sheet which will be far more efficient long term, ie:
Code:
H3: =SUMIF('Warehouse Response'!$H:$H,LEFT($A3,3)&":"&$B3,'Warehouse Response'!$B:$B)
|
07-02-2009, 11:06 AM
|
 |
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,442
|
|
|
Re: IF and VLOOKUP formula.
To me what your attempted formula is referencing does not match what you are describing.. this leads me to confusion as to what it is you really want.
Can you try again.. .also.. I see many quantity columns in the Inventory sheet.. which are we working with?
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
07-02-2009, 11:21 AM
|
|
Valued Forum Contributor
|
|
Join Date: 23 Mar 2008
Location: Tennessee
MS Office Version:Office 2007
Posts: 461
|
|
|
Re: IF and VLOOKUP formula.
Thanks for the response, guys.
Your approach works perfectly, DonkeyOte. Thanks!!
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
