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

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

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

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

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

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