UPDATE & COMPARE

[Doug Van]

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

 

[Frank Kabel]

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,'code'!$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

 

[Doug Van]

Thanks Frank

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

Doug

 

[Doug Van]

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

 

[Frank Kabel]

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

 

[Doug Van]

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,Codes!$A$2:$A$10001&Codes!$
B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MATCH(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]

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,Codes!$A$2:$A$10001&Co
des!$
B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MATCH(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

 

[Doug Van]

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]

That was it, last range was off.

Thanks
Doug

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,Codes!$A$2:$A$10001&Co
des!$
B$2:$B$10001,0))),F18,INDEX(Codes!$E$2:$E$10001,MATCH(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

 

[Doug Van]

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?

 

[Frank Kabel]

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

 

[Doug Van]

I tried that, it is even slower.

That is what I figured. Thanks Frank