accessing data in an array

[cefpe]

In one worksheet I have in one column the job no. and another th
personnel job code. These two coulmns are concatenated in a thir
column as the key, job no.+ job code or 001BL1.
On a second worksheet I have in the top row the personnel job code an
in the left column the job no. The infill is the various pay rates fo
the array as shown below.
BL1 BL2 BL3 BL4 BLn
001 12.50 15.00 25.00 27.50 30.00
002 15.00 etc. ..............................
How can I access the intersection of the array on the second workshee
to the concatenated key on the first worksheet?
I.E. 001BL1 = 12.50.
Thanks,
C. Furta

 

[Max]

One way..

Assuming you have

In Sheet2
-------------
The payrate ref table in cols A to F, data from row2 down:

Job#_BL1__BL2__BL3__BL4__BLn
001 12.50 15.00 25.00 27.50 30.00
002 15.00 20.00 etc. ..............................

In Sheet1
-------------
The set-up in cols A to D, data from row2 down

Job#_Jobcode_Key__Payrate
001__BL1___001BL1_12.5
002__BL2___002BL2__20
etc

To extract the payrates in col D:

Put in D2:

=OFFSET(Sheet2!$A$1,MATCH(TEXT(A2,"000"),Sheet2!$A:$A,0)-1,MATCH(TRIM(B2),Sh
eet2!$1:$1,0)-1)

Copy down

Or, perhaps better with error traps to return ""
for any unmatched job#s or jobcodes:

Put in D2:

=IF(OR(ISNA(MATCH(TEXT(A2,"000"),Sheet2!$A:$A,0)),ISNA(MATCH(TRIM(B2),Sheet2
!$1:$1,0))),"",OFFSET(Sheet2!$A$1,MATCH(TEXT(A2,"000"),Sheet2!$A:$A,0)-1,MAT
CH(TRIM(B2),Sheet2!$1:$1,0)-1))

Copy down

 

[cefpe]

Max,
Thanks, I'll see how this works.
C. Furta

 

[cefpe]

Max,
The example that you posted doesn't use an array on the secon
worksheet. To me an array would be a simpler method to store the data
For example: The top row would have the personnel code and the lef
column would have the job number. The intersections of the row an
coulumn would have the correct pay rate.

Job # A B C
D

BL1 BL2 BL3
BL4
1. 001 12.5 15.00 25.00 30.00
2. 002 " " "
"

How can I look up, for instance, cell B1 that is concatenated on Shee
1 as 001BL2 and get the $15 rate?
Thanks,
C. Furta

 

[Max]

Sorry, I'm not familiar with the intersection operator (single space).
Not sure how / if it can be used in the manner you want.
Perhaps others may you offer their insights ..

But did my suggestion work for you?
If it did, just hold it for use as a reserve option then
(it does do away with the need for a concat col C in Sheet1 <g>)

 

[cefpe]

Max,
I did not try your solution but I will. I am still trying to get th
array answer.
C

 

[hgrove]

cefpe wrote...

In one worksheet I have in one column the job no. and another
the personnel job code. These two coulmns are concatenated in
a third column as the key, job no.+ job code or 001BL1. On a
second worksheet I have in the top row the personnel job code
and in the left column the job no. The infill is the various pay
rates for the array as shown below.

BL1 BL2 BL3 BL4 BLn
001 12.50 15.00 25.00 27.50 30.00
002 15.00 etc. ..............................

How can I access the intersection of the array on the second
worksheet to the concatenated key on the first worksheet? I.E.
001BL1 = 12.50.

Don't use the concatenated key if you have job number and personnel jo
code already separated. Or use the combined key to refer back to th
separate identifiers.

If your job number, personnel job code and combined key table were in
range named Codes, your table of pay rates by job number and personne
job code were in a range named PayRates, and your combined key entr
were in cell named Key, you could use formulas like the following.

INDEX(PayRates,MATCH(INDEX(Codes,MATCH(Key,
INDEX(Codes,0,3),0),1),INDEX(PayRates,0,1),0),
MATCH(INDEX(Codes,MATCH(Key,INDEX(Codes,0,3),0),2),
INDEX(PayRates,1,0),0)

 

[cefpe]

hgrove,
Thanks for the reply and formula.
I'll try it this afternoon and respond to your message.
Thanks,
C. Furta

 

[cefpe]

hgrove,
I just tried your fornula and got the response #N/A in the rate cell.
My first error message was that I need a ) so I added one at the end o
the formula and this was accepted by Excel.
The PayRates table is set up as an array with the personnel job code i
the rows and the job number in the columns. The rates are then filled i
below and between.
The Codes Table has 8 columns; personnel ID, Full name, last name (fo
sorting), SS number, job number, trade code, key ( combining job numbe
and trade code) and finally the rate. Since I don't understand all o
the formula, does the rate being in column 8 affect your formula?
C

 

[Harlan Grove]

I just tried your fornula and got the response #N/A in the rate cell.
My first error message was that I need a ) so I added one at the end of
the formula and this was accepted by Excel.

The formula I gave, even though it didn't include an initial = did have
balanced left and right parentheses, so you either mistyped it or if you
pasted it you may have left off the final right parenthesis.

I tested the formula with a simple setup. One thing that could cause #N/A
return values is stray spaces in the job numbers or personnel job codes in
the second (PayRates) table.

The PayRates table is set up as an array with the personnel job code in
the rows and the job number in the columns. The rates are then filled in
below and between.
The Codes Table has 8 columns; personnel ID, Full name, last name (for
sorting), SS number, job number, trade code, key ( combining job number
and trade code) and finally the rate. Since I don't understand all of
the formula, does the rate being in column 8 affect your formula?

Yup, additional columns would fubar my formula. You'd need to change the
formula to

=INDEX(PayRates,MATCH(INDEX(Codes,MATCH(Key,
INDEX(Codes,0,7),0),5),INDEX(PayRates,0,1),0),
MATCH(INDEX(Codes,MATCH(Key,INDEX(Codes,0,7),0),6),
INDEX(PayRates,1,0),0))

 

[cefpe]

hgrove,
I am getting closer, I think?
I have use your latest formula but changed the Key entry to the cel
reference or $G4. I now get a reference error.
My PayRates range is C4:Z200 and the Codes Range is E4:G200.
Maybe my ranges do not match to your offsets.
I understand the Codes 0,7 but what does the 5 do? Also, what are th
offsets for the PayRates of 0,1 or the 0,6 at the end of the thir
line.
I also looked into the help in Excel and the help info. describes usin
CTRL+Shift+Enter for an array formula?
Again, your help is appreciated.
C

 

[hgrove]

cefpe wrote...

I have use your latest formula but changed the Key entry to the
cell reference or $G4. I now get a reference error.
My PayRates range is C4:Z200 and the Codes Range is E4:G200.

In your previous follow-up you wrote:

"The Codes Table has 8 columns; personnel ID, Full name, last name (fo
sorting), SS number, job number, trade code, key ( combining job numbe
and trade code) and finally the rate. Since I don't understand all o
the formula, does the rate being in column 8 affect your formula?"

I interpretted this to mean that I had to look up the key value i
column 7 of an 8 column range and return the corresponding values fro
columns 5 and 6. If you've changed the Codes range to span just the 5t
through 7th columns of the 8 column range, then my original formul
should work but the revised formula I gave you in my last respons
would return #REF!

Perhaps I need to deconstruct the original formula.

=INDEX(PayRates,

_ You want a value from the PayRates table.

MATCH(INDEX(Codes,

You want a job number from the Codes table.

MATCH(Key, INDEX(Codes,0, 3 ) ,0),

This looks for Key in the *3rd* column of Codes . . .

1 ),

. . . and returns the corresponding 1st column entry.

INDEX(PayRates,0,1),0),

Then locates the corresponding row in the PayRates table.

MATCH(INDEX(Codes,

You want a personnel job code from the Codes table.

MATCH(Key, INDEX(Codes,0, 3 ), 0),

This also looks for Key in the *3rd* column of Codes . . .

2 ),

. . . but this time returns the corresponding 2nd column entry.

INDEX(PayRates,1,0),0))

Then locates the corresponding column in the PayRates table.

So, rewriting this using _KeyCol_, _JobNum_ and _PersJobCode_
respectively, for the columns in the Codes table holding the ke
values, the job numbers and the personnel job codes, the formula woul
look like

INDEX(PayRates,MATCH(INDEX(Codes,
MATCH(Key,INDEX(Codes,0, _KeyCol_ ),0), _JobNum_ ),
INDEX(PayRates,0,1),0),MATCH(INDEX(Codes,
MATCH(Key,INDEX(Codes,0, _KeyCol_ ),0), _PersJobCode_ ),
INDEX(PayRates,1,0),0))

If Codes spans 3 columns, _KeyCol_ must be 3, _JobNum_ must be 1 an
_PersJobCode_ must be 2. If Code spans 8 columns as per your previou
follow-up, _KeyCol_ must be 7, _JobNum_ must be 5 and _PersJobCode
must be 6

 

[cefpe]

hgrove,
I guess that my writing was not clear and is my fault. The Codes tabl
has 8 columns but the range only has 3 columns on that sheet. Th
columns are (E) Job Number, (F) Trade Code, and (G) the Key or th
combination of E & F.
The PayRates range may encompass many rows and columns depending on th
companys activity.
It now appears that your first formula may work and I'll try i
shortly. (My wife is calling)
By using the range names I assume that it allows one worksheet t
reference the other?
Thanks again for your patience, interest and help.
C

 

[cefpe]

hgrove,
It finally worke once I included the labels in the named ranges.
Thanks again.
C

 

[cefpe]

hgrove,
The formula works fine but one last question. How can I surpress th
N/A when there is no data in the preceeding cells?
Thanks,
C

 

[Frank Kabel]

Hi
use somethink like
=IF(ISNA(your_formula),"",your_formula)

 

[cefpe]

Frank,
If I understand your message I should use the last formula in the pos
and use the IF(ISNA) before the above formula that is used twice an
separated by the ,"",. Is this true? Can I use a 0 between the "0".?
C

 

[Frank Kabel]

Hi
of course you can. Use
=IF(ISNA(formula),0,formula)