ExcelBanter - need a fuction that can do this (or can it be done)

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