• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a new formula

R

RUSSW

New Member
Hi


Please can you help me to create a new formula for the following


I have 2 worksheets that both contain stock codes in Column A


Worksheet A has a few thousand stock codes in column A

Worksheet B is a Stock on hand worksheet and only has the stock codes that have current stock on hand.


On worksheet A i am am trying to create a formula in column D that will look at worksheet B and if it finds the relevant stock code then i want i to pull the qty for that stock code from column C
 
Russw


Firstly, Welcome to the Chandoo.org forums.


Assuming both sheets have a Header Row

in Worksheet A Cell D2

Code: 
=Index('Worksheet B'!C:C,Match(A2,'Worksheet B'!A:A, 0))


Adjust A:A and C:C to be the range of your data

Both should be the same length
 
thanks Hui


Yes both sheets have a header row and i hav adjusted the range of data but this doesnt seem to be working.


Range in Worksheet A is 1533 and in worksheet B it is 446
 
The following should do it

Code: 
=INDEX('Worksheet B'!$C$2:$C$446,MATCH(A2,'Worksheet B'!$A$2:$A$446, 0))

You will need to change the worksheet names to suit

This must be placed on Worksheet A cell D2

Then Copy Down


If it doesn't work can you please post a sample file

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Unable to upload due to company restrictions.

But i dont see anywhere that you comparing the stock code between worksheet A and Worksheet B or is that what the match is for?
 
RussW


MATCH(A2,'Worksheet B'!$A$2:$A$446, 0)

Find the location (Position Number) of the value in Worksheet A A2 from Column A of Worksheet B


=INDEX('Worksheet B'!$C$2:$C$446,Location from Match )

Index retrieves the Values from Location from Worksheet B Column C


You may want to check that the values in WS-A A2 and WS-B Column A don't have leading or trailing spaces


<- You can email me if you want

Click on Excel Ninja

Email at Bottom of page
 
Hi Russw,


Try this.

=VLOOKUP(A2,'Worksheet B'!$A$2:$B$1533,2,0)

Put this in worksheet A cell D2 and copy down.

You might want to adjust the range as I have tried using just 2 columns.


Jai
 
Hui


I have tried the MATCH on its own and that works and gives me relevant positions but it comes up with a error when i try the whole formula
 
this one works


=MATCH(A3;$'Stock count 070712_2'.$A$3:$A$447;0)


But when i try

=INDEX('Stock count 070712_2'.$C$3:$C$447,MATCH(A3;$'Stock count 070712_2'.$A$3:$A$447;0))


this comes up with a err:508
 
@RUSSW


None of your formula is working in my excel, I think you should use " , " in place of ";" in your match formula. Hui's formula shall work.
 
RussW


RE: Match Problem


In your file, Stockcode 00.2415.019.020 doesn't exist in Stock count 070712_2 and so Excel returns an error


a simple change to D3:

Code: 
=IFERROR(MATCH(A3,'Stock count 070712_2'!$A$3:$A$447,0),0)

or

=IFERROR(MATCH(A3,'Stock count 070712_2'!$A$3:$A$447,0),"Doesn't exist")

will fix that
 
and answering your original question

Code: 
=IFERROR(INDEX('Stock count 070712_2'!$D$3:$D$447,MATCH(A3,'Stock count 070712_2'!$A$3:$A$447,0),),0)

Will retrieve the values from Column D which match column A

and 0 if there is no match
 
You must log in or register to reply here.
Back
Top