Forum Discussion
Beginners Excel Lookup Question
Hi all,
Please don’t fall down laughing when you read this.
I want to return the “Qty Available” by each “Warehouse Code”
The below output table should only show 1 x available in 612 and 935 but show 0 x available in 610:
| 610 | 612 | 935 | |
| Item Code | Qty Available | Qty Available | Qty Available |
| 1103977 |
On a second tab I have the data stored:
| Category | Item Code | Warehouse Code | Min | Max | Qty In Transit | Qty Available | Qty On Hand |
| WATCHES | 1103977 | 612 | 0 | 0 | 0 | 1 | 1 |
| WATCHES | 1103977 | 935 | 0 | 0 | 0 | 2 | 2 |
A standard V Lookup does not look at the warehouse code so what is the trick for Excel to look at both the Item Code and Warehouse Code to return the Qty Available?
Many thanks
- Sorry, I should correct the above:
"The below output table should only show 1 x available in 612 and "2 x in 935" but show 0 x available in 610:Scott,
=IFERROR(LOOKUP(2,1/(Tabelle2!$B$2:$B$3=$A3)/(Tabelle2!$C$2:$C$3=B$1),Tabelle2!$G$2:$G$3),0)
In your output table, assuming it begins in cell A1 on Sheet1 and the other tab is Sheet2, enter in B3:
=SUMIFS(Sheet2!$G:$G,Sheet2!$B:$B,$A3,Sheet2!$C:$C,B$1)
Which is saying SUM Sheet2 column G IF Sheet 2 Item Code = "1103977" AND Sheet2 Warehouse Code = "610".
Drag to the right to get the other two available amounts.
The left-aligned numbers in your question suggest they are "numbers stored as text" which means you will have to convert column G to numbers to text.
