One play to try ..
Assume you have in Sheet2, in cols A to D from row2 down,
headers in row2, data from row3 down:
WWHS WPROD IMIN SOH01
AB RAS-13UKH-E3 1 90
AB 42P100CX 2 25
etc
(The figures: 90, 25 are in col D [SOH01])
In Sheet1
-----------
In B1 will be input the "WWHS" detail, e.g.: AB
In A2 will be input the "WPROD" detail, e.g.: RAS-13UKH-E3
C2 contains the header: Stock ON HAND
And C3 is where you want to retrieve the figure
from col D ("SOH01") in Sheet2 corresponding to the inputs in B1 & A2
Put in the formula bar for C3, and array-enter the formula,
i.e. press CTRL+SHIFT+ENTER :
=IF(OR(B1="",A2=""),"",INDEX(Sheet2!D3:D9000,MATCH (B1&"_"&A2,Sheet2!A3:A9000
&"_"&Sheet2!B3:B9000,0)))
For the sample inputs in B1 & A2 above, C3 will return: 90
And if you change the input in A2 to: 42P100CX,
C3 will return: 25
If there's no match found for the inputs in B1 & A2,
C3 will return: #N/A
To trap the ugly #N/A returns, put instead in C3,
and array-enter as before, the slightly longer:
=IF(OR(B1="",A2=""),"",IF(ISNA(MATCH(B1&"_"&A2,She et2!A3:A9000&"_"&Sheet2!B3
:B9000,0)),"",INDEX(Sheet2!D3:D9000,MATCH(B1&"_"&A 2,Sheet2!A3:A9000&"_"&Shee
t2!B3:B9000,0))))
Unmatched cases will now return blanks: ""
Adjust the 3 ranges in the formula to suit:
Sheet2!D3:D9000
Sheet2!A3:A9000
Sheet2!B3:B9000
Note that the 3 ranges should be identically structured
and we can't use entire col references in the array formula
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Mrbanner" wrote in message
oups.com...
Hi guys need help with the following
Trying to do like a lookup or something .
i have 2 documents
Doc 1 is my main document what i am trying to do is.
Get the info from document 2 to document 1 in the correct line.
So i need excel to do the following
If b1(doc1)= Colum a(doc2) there is like 9000 lines
And a2(doc1)= Colum b(doc2) Again around 9000 lines
The input Colum d(doc2) into colum c (doc1)
Can this be donE?
The reason i need it to match 2 things is there could be 2 items the
same but diffrent letters in Colum a (doc 2)
Any help would be great
dOC 1
A B C D E
1 ITEMS AB
2 CKO Stock ON HAND MIN CUST ORD
3 42P100CX 25 25 5
doc 2
A B C D E
1
2 WWHS WPROD IMIN SOH01
3 AB RAS-13UKH-E3 1 0 0
4 AB RAS-13UAH-E3 1 0 0