UPDATE & COMPARE
 

I have two tabs on a spreadsheet, one called import and the other called
lookup.

I need to update one of the column in the lookup table based on a match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there is a match, update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am close, I just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

Hi
you may use a helper column with the following array formula (entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:
==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co
de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and the other
called
lookup.

I need to update one of the column in the lookup table based on a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there is a match,
update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am close, I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

Thanks Frank

Can this be entered into a macro so the user doesn't have to do this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array formula (entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:
==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co
de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and the other
called
lookup.

I need to update one of the column in the lookup table based on a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there is a match,
update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am close, I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

Will this work if there is a different number of rows. I could have my
'lookup' spreadsheet with only one entry for the day (changes day to day),
but the 'code' could have 10,000.

C1,INDEX (What is the C1 for in the forumla, it is returning the data from
the 'code' spreadsheet, not the 'lookup')

Please advise




"Doug Van" wrote in message
...
Thanks Frank

Can this be entered into a macro so the user doesn't have to do this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array formula (entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:
==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co
de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and the other
called
lookup.

I need to update one of the column in the lookup table based on a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there is a match,
update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am close, I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

Hi Doug
C1 is the current number on your lookup sheet.
If you copy this formula down it will adapt automatically

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
Will this work if there is a different number of rows. I could have
my
'lookup' spreadsheet with only one entry for the day (changes day to
day),
but the 'code' could have 10,000.

C1,INDEX (What is the C1 for in the forumla, it is returning the data
from
the 'code' spreadsheet, not the 'lookup')

Please advise




"Doug Van" wrote in message
...
Thanks Frank

Can this be entered into a macro so the user doesn't have to do
this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array formula
(entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:

==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co

de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and the
other
called
lookup.

I need to update one of the column in the lookup table based on
a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there is a
match,
update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am close,
I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

I got that Frank.

Very close, here is the formula I am using. It is grabbing the wrong
number. I copied this from row 18.

=IF(ISNA(INDEX(Codes!$E$2:$E$10001,MATCH(B18&C18,C odes!$A$2:$A$10001&Codes!$
B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MA TCH(B18&C18,Codes!$A$1:$A$
10001&Codes!$B$1:$B$10001,0)))

Code Table Value
E - Number 9998
A - Description Maintenance 2006
B - State WI

Parent Table Row 18 (Value)
B - Description Maintenance 2006
C - State WI
F - Code Blank
E - Helper Column 2423

It should be grabbing 9998.

Let me know when you get a chance.

Thanks,
Doug





"Frank Kabel" wrote in message
...
Hi Doug
C1 is the current number on your lookup sheet.
If you copy this formula down it will adapt automatically

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
Will this work if there is a different number of rows. I could have
my
'lookup' spreadsheet with only one entry for the day (changes day to
day),
but the 'code' could have 10,000.

C1,INDEX (What is the C1 for in the forumla, it is returning the data
from
the 'code' spreadsheet, not the 'lookup')

Please advise




"Doug Van" wrote in message
...
Thanks Frank

Can this be entered into a macro so the user doesn't have to do
this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array formula
(entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:

==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co

de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and the
other
called
lookup.

I need to update one of the column in the lookup table based on
a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there is a
match,
update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am close,
I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

Hi
any chance that there's a match before this entry. If you like, email
me your file' and I'll have a look at it. But before try
=IF(ISNA(INDEX(Codes!$E$2:$E$10001,MATCH(B18&C18,C odes!$A$2:$A$10001&Co
des!$
B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MA TCH(B18&C18,Codes!$A$
2:$A$
10001&Codes!$B$2:$B$10001,0)))

correcting the last range in your formula from $A$1.. to $A$2

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I got that Frank.

Very close, here is the formula I am using. It is grabbing the wrong
number. I copied this from row 18.


=IF(ISNA(INDEX(Codes!$E$2:$E$10001,MATCH(B18&C18,C odes!$A$2:$A$10001&Co
des!$

B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MA TCH(B18&C18,Codes!$A$
1:$A$
10001&Codes!$B$1:$B$10001,0)))

Code Table Value
E - Number 9998
A - Description Maintenance 2006
B - State WI

Parent Table Row 18 (Value)
B - Description Maintenance 2006
C - State WI
F - Code Blank
E - Helper Column 2423

It should be grabbing 9998.

Let me know when you get a chance.

Thanks,
Doug





"Frank Kabel" wrote in message
...
Hi Doug
C1 is the current number on your lookup sheet.
If you copy this formula down it will adapt automatically

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
Will this work if there is a different number of rows. I could
have
my
'lookup' spreadsheet with only one entry for the day (changes day
to
day),
but the 'code' could have 10,000.

C1,INDEX (What is the C1 for in the forumla, it is returning the
data
from
the 'code' spreadsheet, not the 'lookup')

Please advise




"Doug Van" wrote in message
...
Thanks Frank

Can this be entered into a macro so the user doesn't have to do
this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array formula
(entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:


==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co


de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and the
other
called
lookup.

I need to update one of the column in the lookup table
based on
a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there
is a
match,
update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am
close,
I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

Not sure if this helps or not, but it looks like if it finds a match, it is
grabbing the next rows value, not the current rows value.

"Doug Van" wrote in message
...
I got that Frank.

Very close, here is the formula I am using. It is grabbing the wrong
number. I copied this from row 18.


=IF(ISNA(INDEX(Codes!$E$2:$E$10001,MATCH(B18&C18,C odes!$A$2:$A$10001&Codes!$

B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MA TCH(B18&C18,Codes!$A$1:$A$
10001&Codes!$B$1:$B$10001,0)))

Code Table Value
E - Number 9998
A - Description Maintenance 2006
B - State WI

Parent Table Row 18 (Value)
B - Description Maintenance 2006
C - State WI
F - Code Blank
E - Helper Column 2423

It should be grabbing 9998.

Let me know when you get a chance.

Thanks,
Doug





"Frank Kabel" wrote in message
...
Hi Doug
C1 is the current number on your lookup sheet.
If you copy this formula down it will adapt automatically

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
Will this work if there is a different number of rows. I could have
my
'lookup' spreadsheet with only one entry for the day (changes day to
day),
but the 'code' could have 10,000.

C1,INDEX (What is the C1 for in the forumla, it is returning the data
from
the 'code' spreadsheet, not the 'lookup')

Please advise




"Doug Van" wrote in message
...
Thanks Frank

Can this be entered into a macro so the user doesn't have to do
this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array formula
(entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:

==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co

de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and the
other
called
lookup.

I need to update one of the column in the lookup table based on
a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there is a
match,
update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am close,
I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

That was it, last range was off.

Thanks
Doug

"Frank Kabel" wrote in message
...
Hi
any chance that there's a match before this entry. If you like, email
me your file' and I'll have a look at it. But before try
=IF(ISNA(INDEX(Codes!$E$2:$E$10001,MATCH(B18&C18,C odes!$A$2:$A$10001&Co
des!$
B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MA TCH(B18&C18,Codes!$A$
2:$A$
10001&Codes!$B$2:$B$10001,0)))

correcting the last range in your formula from $A$1.. to $A$2

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I got that Frank.

Very close, here is the formula I am using. It is grabbing the wrong
number. I copied this from row 18.


=IF(ISNA(INDEX(Codes!$E$2:$E$10001,MATCH(B18&C18,C odes!$A$2:$A$10001&Co
des!$

B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MA TCH(B18&C18,Codes!$A$
1:$A$
10001&Codes!$B$1:$B$10001,0)))

Code Table Value
E - Number 9998
A - Description Maintenance 2006
B - State WI

Parent Table Row 18 (Value)
B - Description Maintenance 2006
C - State WI
F - Code Blank
E - Helper Column 2423

It should be grabbing 9998.

Let me know when you get a chance.

Thanks,
Doug





"Frank Kabel" wrote in message
...
Hi Doug
C1 is the current number on your lookup sheet.
If you copy this formula down it will adapt automatically

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
Will this work if there is a different number of rows. I could
have
my
'lookup' spreadsheet with only one entry for the day (changes day
to
day),
but the 'code' could have 10,000.

C1,INDEX (What is the C1 for in the forumla, it is returning the
data
from
the 'code' spreadsheet, not the 'lookup')

Please advise




"Doug Van" wrote in message
...
Thanks Frank

Can this be entered into a macro so the user doesn't have to do
this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array formula
(entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:


==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co


de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and the
other
called
lookup.

I need to update one of the column in the lookup table
based on
a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there
is a
match,
update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am
close,
I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

OK. This is working as it should now. Thanks Frank.

The problem, I am having now is it is soooooo slow.
I know it is not my machine, 2.6 ghz, with 1 gig of RAM and 100mb network
connection.

Is there anything I can do to check why this is slow?


"Doug Van" wrote in message
...
Not sure if this helps or not, but it looks like if it finds a match, it
is
grabbing the next rows value, not the current rows value.

"Doug Van" wrote in message
...
I got that Frank.

Very close, here is the formula I am using. It is grabbing the wrong
number. I copied this from row 18.



=IF(ISNA(INDEX(Codes!$E$2:$E$10001,MATCH(B18&C18,C odes!$A$2:$A$10001&Codes!$


B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MA TCH(B18&C18,Codes!$A$1:$A$
10001&Codes!$B$1:$B$10001,0)))

Code Table Value
E - Number 9998
A - Description Maintenance 2006
B - State WI

Parent Table Row 18 (Value)
B - Description Maintenance 2006
C - State WI
F - Code Blank
E - Helper Column 2423

It should be grabbing 9998.

Let me know when you get a chance.

Thanks,
Doug





"Frank Kabel" wrote in message
...
Hi Doug
C1 is the current number on your lookup sheet.
If you copy this formula down it will adapt automatically

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
Will this work if there is a different number of rows. I could have
my
'lookup' spreadsheet with only one entry for the day (changes day to
day),
but the 'code' could have 10,000.

C1,INDEX (What is the C1 for in the forumla, it is returning the
data
from
the 'code' spreadsheet, not the 'lookup')

Please advise




"Doug Van" wrote in message
...
Thanks Frank

Can this be entered into a macro so the user doesn't have to do
this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array formula
(entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:


==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co


de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and the
other
called
lookup.

I need to update one of the column in the lookup table based
on
a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If there is a
match,
update
the Number column (lookup) with the value in Code (Import).

Would the lookup function be the best way? I think I am
close,
I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

Hi
your range is quite large and I'm not sure how many of these formulas
you use. You may try setting the calculation mode to manual and only
re-calculate on demand

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
OK. This is working as it should now. Thanks Frank.

The problem, I am having now is it is soooooo slow.
I know it is not my machine, 2.6 ghz, with 1 gig of RAM and 100mb
network
connection.

Is there anything I can do to check why this is slow?


"Doug Van" wrote in message
...
Not sure if this helps or not, but it looks like if it finds a
match, it
is
grabbing the next rows value, not the current rows value.

"Doug Van" wrote in message
...
I got that Frank.

Very close, here is the formula I am using. It is grabbing the
wrong
number. I copied this from row 18.




=IF(ISNA(INDEX(Codes!$E$2:$E$10001,MATCH(B18&C18,C odes!$A$2:$A$10001&Co
des!$



B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MA TCH(B18&C18,Codes!$A$
1:$A$
10001&Codes!$B$1:$B$10001,0)))

Code Table Value
E - Number 9998
A - Description Maintenance 2006
B - State WI

Parent Table Row 18 (Value)
B - Description Maintenance 2006
C - State WI
F - Code Blank
E - Helper Column 2423

It should be grabbing 9998.

Let me know when you get a chance.

Thanks,
Doug





"Frank Kabel" wrote in message
...
Hi Doug
C1 is the current number on your lookup sheet.
If you copy this formula down it will adapt automatically

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
Will this work if there is a different number of rows. I
could have
my
'lookup' spreadsheet with only one entry for the day (changes
day to
day),
but the 'code' could have 10,000.

C1,INDEX (What is the C1 for in the forumla, it is returning
the
data
from
the 'code' spreadsheet, not the 'lookup')

Please advise




"Doug Van" wrote in message
...
Thanks Frank

Can this be entered into a macro so the user doesn't have
to do
this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array
formula
(entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:



==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co



de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and
the
other
called
lookup.

I need to update one of the column in the lookup table
based
on
a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If
there is a
match,
update
the Number column (lookup) with the value in Code
(Import).

Would the lookup function be the best way? I think I
am
close,
I
just need
someone to point me in the right direction.

Thanks

UPDATE & COMPARE
 

I tried that, it is even slower.

That is what I figured. Thanks Frank


"Frank Kabel" wrote in message
...
Hi
your range is quite large and I'm not sure how many of these formulas
you use. You may try setting the calculation mode to manual and only
re-calculate on demand

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
OK. This is working as it should now. Thanks Frank.

The problem, I am having now is it is soooooo slow.
I know it is not my machine, 2.6 ghz, with 1 gig of RAM and 100mb
network
connection.

Is there anything I can do to check why this is slow?


"Doug Van" wrote in message
...
Not sure if this helps or not, but it looks like if it finds a
match, it
is
grabbing the next rows value, not the current rows value.

"Doug Van" wrote in message
...
I got that Frank.

Very close, here is the formula I am using. It is grabbing the
wrong
number. I copied this from row 18.




=IF(ISNA(INDEX(Codes!$E$2:$E$10001,MATCH(B18&C18,C odes!$A$2:$A$10001&Co
des!$



B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MA TCH(B18&C18,Codes!$A$
1:$A$
10001&Codes!$B$1:$B$10001,0)))

Code Table Value
E - Number 9998
A - Description Maintenance 2006
B - State WI

Parent Table Row 18 (Value)
B - Description Maintenance 2006
C - State WI
F - Code Blank
E - Helper Column 2423

It should be grabbing 9998.

Let me know when you get a chance.

Thanks,
Doug





"Frank Kabel" wrote in message
...
Hi Doug
C1 is the current number on your lookup sheet.
If you copy this formula down it will adapt automatically

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
Will this work if there is a different number of rows. I
could have
my
'lookup' spreadsheet with only one entry for the day (changes
day to
day),
but the 'code' could have 10,000.

C1,INDEX (What is the C1 for in the forumla, it is returning
the
data
from
the 'code' spreadsheet, not the 'lookup')

Please advise




"Doug Van" wrote in message
...
Thanks Frank

Can this be entered into a macro so the user doesn't have
to do
this
everyday.

Doug

"Frank Kabel" wrote in message
...
Hi
you may use a helper column with the following array
formula
(entered
with CTRL+SHIFT+ENTER) in cell D1 on your lookup sheet:



==IF(ISNA(INDEX('code'!$C$1:$C$1000,MATCH(A1&B1,'c ode'!$A$1:$A$1000&'co



de'!$B$1:$B$1000,0))),C1,INDEX('code'!$C$1:$C$1000 ,MATCH(A1&B1,'code'!$
A$1:$A$1000&'code'!$B$1:$B$1000,0)))
and copy down for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug Van" schrieb im Newsbeitrag
...
I have two tabs on a spreadsheet, one called import and
the
other
called
lookup.

I need to update one of the column in the lookup table
based
on
a
match.

For example

Import has three columns
Job
State
Code

Lookup has three column
Job
State
Number

If want to search for a match on Job and State. If
there is a
match,
update
the Number column (lookup) with the value in Code
(Import).

Would the lookup function be the best way? I think I
am
close,
I
just need
someone to point me in the right direction.

Thanks