LinkBack URL
About LinkBacksOne 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,Sheet2!A3:A9000&"_"&Sheet2!B3
:B9000,0)),"",INDEX(Sheet2!D3:D9000,MATCH(B1&"_"&A2,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 <at>yahoo<dot>com
----
"Mrbanner" <[email protected]> wrote in message
news:[email protected]...
> 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
>
Register To Reply
Bookmarks