Need lookup based on counter

[dsathiya.infotech]

Dear friends,
I am trying to lookup available spare parts from a file to another file based on job id.
But if particular spare is only 10 then it should not mapped for 11th Job id.

How can I achive this.

[AliGW]

Sorry, there is nowhere near enough information here.

Please provide some sample data and expected outcomes.

[dsathiya.infotech]

Hi,

I have 2 Sheets Named Requirement and Stock.

need to fetch data from Stock sheet.

Stock
JoB ID City Status
101 ASK Available
101 ASK Available
101 QLD Available
101 ASK Available
101 QLD Available
101 DSP Available
102 ASK Available
102 QLD Available
102 DSP Available
102 ASK Available
102 QLD Available
102 DSP Available
102 ASK Available
155 QLD Available
155 DSP Available
155 ASK Available
155 QLD Available
155 DSP Available
155 ASK Available
155 QLD Available


Requirement
JoB ID City Status
101 ASK
101 ASK
101 ASK
101 ASK
101 ASK
102 ASK
155 ASK
155 ASK
155 ASK
155 QLD
102 QLD
102 ASK
102 ASK
102 ASK
101 QLD
101 QLD
101 QLD


In requirement sheet need to fill Status column based on Stock sheet.

Where if example , 101 job ID in City ASK is only 2 in stock sheet.
then it should be mapped only to 2 Job ID in Requirement sheet .
even if job id and city matched for 3rd entry we should be no stock or blank.

[kvsrinivasamurthy]

ARRAY formula is used.Pl see note below.
In C2 of Requirement

Please Login or Register  to view this content.

[sourabhg98]

I dont think there's a need to go into array formulas here.

In Requirement sheet cell C2 use this formula -

Please Login or Register  to view this content.

Sample attached. Excel Forum 1262.xlsx