how would i wirte this formula

[Marq]

all im about to write takes place in same workbook

ok...I have a column of SAP numbers (for example 90312790)....say 100 rows long....all these sap numbers are in Column B on a sheet named DATA.

On a sheet named MAIN I have the SAP number with the associated work package...for example in Column A I will have the names of the packages..like PV-1561...in column B Ill have its approriate SAP number so it would look like this

A B
PV-1561 90312790
PV-1792 90333425
P034556 90387490

The packages are column A...the saps are column B...this is on the MAIN sheet

On the Data sheet I have hundres of rows of info and Column B is the column holding the SAP numbers.......the SAP numbers will be repeated randomly in column B, and theres tons of individual SAPs ...I cant sort thm by column B because I have them sorted by date in column D, and it has to stay this way.....for example for date 1-10-05, ill have 20 rows of data...and so on and so on for every day.....withing that 20 rows of data for 1-10-05 sap number 9037685 could be repated 4 times

I would like to have a formula in column C on DATA sheet that says basically:

If SAP in column B, cell B4 on Data sheet is equal to ANY sap number located in column B on Main sheet, then cell B4 on Data sheet equals that assocciated package

so basically I want the package number to pop up next to the SAP on Data sheet......I owuld then copy and paste this formula all the way down column C in Data sheet so ill have the package name next to each sap that is in column B on Data sheet

i have so many SAPs to track its hard to distinguish by looking at the number...having the package name next to it would be a huge help

Hope I explained myself enough...this formula seems like its do-able....I just dont know how to write it

anyone?

Marq

 

[LEXCERM]

Marq,

At first glance, you would have to initially swap columns A & B on the MAIN sheet so that column A has the SAP numbers and column B has the package details.

Then in column C of the DATA sheet enter this formula:-
=VLOOKUP(B2,main!A:B,2,0)

Hope this helps.

Regards,
PAUL.

 

[Tazguy37]

Or try this:
=INDEX(MAIN!$A$1:$B$1000,MATCH(B1,MAIN!$B$1:$B$1000,0),1)

In A1 of Sheet DATA, and copy down. Hope that helps!

 

[Marq]

i went with the index formula.....working like a charm

now to take it one step further, I want to copy and paste this formula all the way down fo say row 1000.......my daya will not hit row 1000 for months, but id like to have the formula in the cell in column B ready and wiating so to speak.

so i did drag the formula down for row 1000....and I get the #N/A error, because the cells have nothing to calculate until its row is full with data (i.e. SAP number)

and this is ok.....not really an issue.......but how do I make the #n/a error disappear? to where the cell is either blank...or has the package number in it

(all this data sheet of course)

bur for the record the formula works perfect

Thank you for your help

Marq