Couple of Questions

Ed,

Care to elaborate/illustrate these 3 questions with actual formulas that you tried?

Aladin

Excel has a LOOKUP function, which it appears you have discovered. It also has a VLOOKUP and HLOOKUP which I think is what you want. These latter two require an exact match, which is what I think you want. Check the help file for specific information.

Don't really understand the other two questions.

Aladin

1. I have source data of employees and grades. If I enter an employees name into a cell and use Vlookup in the following format vlookup(employees name, source data,increment) in this example the employees name is a cell reference, if the employee name is in the source data I get an answer excellent. If the employee name entered is not in the source data rather than give an error or #NA it duplicates somebody elses grade - leaving me to think I employ that person.

2. I have a 2 way table of data, months and factories. Then I have a summary sheet where I used to key in month and factory. The index function would search for these specific row and column headings and return the appropriate cell contents. Index in excell appears to want a predetermind column and row offset e.g index(data,2,2). But the factory I key may move to column 3 or 4 etc which this formula takes no account of. Equally entering match instead means that if the factory is not in that particular block of data again like vlookup it returns a duplicate entry.

3. Had 62.32CR in a cell, this is £62.32 credit which has been imported as text, I want to display as -62.32, ie negative number. Tried following formula. if(right(a2,2)="CR","credit value","") this would alert me to the prescense of a text value in my data which I can manually amend. However I can't get it to recognise the ="CR" bit tried everything.

Ed

Ed, 1. I have source data of employees and grades. If I enter an employees name into a cell and use Vlookup in the following format vlookup(employees name, source data,increment) in this example the employees name is a cell reference, if the employee name is in the source data I get an answer excellent. If the employee name entered is not in the source data rather than give an error or #NA it duplicates somebody elses grade - leaving me to think I employ that person.

Change your vlookup-formula to:

=vlookup(employees name, source data,increment,0)

0 (means FALSE) forces vlookup to do an exact match. This 4th arg of vlookup can be either 1 or 0 (TRUE or FALSE). If you leave it out, it defaults to 1 which makes vlookup to attempt an approximate match.

I think you have here a situation where you can use:

=OFFSET(the-anchor-cell-ref,match(the-cell-month-of-interest,the-range-in-the-first-column-of-your-data-excluding-anchor-cell,0),match(the-cell-containing-factory-of-interest,the-range-in-the-first-row-of-your-data-excluding-anchor-cell,0))

0 again forces MATCH to do an exact match.

Gee, this is a pretty convoluted description. What follows is an example.

=OFFSET($A$4,MATCH($A$1,$A$5:$A$10,0),MATCH($A$2,$B$4:$D$4,0))

where A1 houses the month of interest, A2 the factory of interest, A5:A10 the months, B4:D4 the factories. The data reside thus in A4:D10, where A4 (the anchor cell) is empty. 3. Had 62.32CR in a cell, this is £62.32 credit which has been imported as text, I want to display as -62.32, ie negative number. Tried following formula. if(right(a2,2)="CR","credit value","") this would alert me to the prescense of a text value in my data which I can manually amend. However I can't get it to recognise the ="CR" bit tried everything.

This is easy. If CR is re-occurring thing in the imported numbers, the following will work:

=-1*SUBSTITUTE(A2,"CR","")

The result of substitute is text-formatted number. Multiplying it with -1 converts it to a negative number.

Aladin Ed

Superb many thanks for your help.

Ed