Thread: IF and VLOOKUP formula.

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.

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)

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?