Need a macro for looking up data

[kestermd]

I have a parameter set that may or maynot always be in may weekly data set. I need to have an automatic way of going through the production data and having three different numbers pulled from data that I am given.

I have tried lookups, vlookup, and indexing it just doesn't seem to work. All i get if that particular parameter wasn't included in this data set for this week (but is in overall parameters) is the value from column were is should have been squeezed in.

Example

Targets
A 2.0 8.0
B 3.5 9.3
C 2.1 3.9
D 4.0 4.1

My Data for this week
A 1.3 7.8
B 3.0 6.8
D 3.8 3.8

My Chart to fill in
A _1.3_ _7.8_
B _3.0_ _6.8_
C _3.0_ _6.8_ I shouldn't have any data here at all. I should all 0's.
D _4.0_ _4.1_

I should fill in my chart with 0's to begin with and then have it do sometype of macro, but I don't know what type and I don't know how to write it.

Could someone help me?

 

[pushkardey]

I personally think macro is unnecessary here you can use the following fromulae:

for Excel 2003:
=IF(ISERROR(VLOOKUP($D9,$B$3:$D$6,2,0)),0,VLOOKUP($D9,$B$3:$D$6,2,0))

for Excel 2007:
=IFERROR(VLOOKUP($D9,$B$3:$D$6,2,0),0)

please change lookup_value, lookup_range and column_number as required....

Hope this helps...

 

[kestermd]

This is sweet! Thanks!

 

[kestermd]

How do you get the data range to be variable? Each time I pull the data into my spreadsheet, it will be a different length. Is there an easy way to that part?

 

[pushkardey]

sure... suppose you your data is from A1 to C6 and next day it grows to C8 the you can use the following formula:

=IFERROR(VLOOKUP($D9,$A:$C,2,0),0)

However make sure that there is nothing else except data in Column A to C

Since we are not sure of the length of data we allocate the whole column for that...
However if you are sure of the max length of data suppose 200 you can use the following formula (make sure there are no blanks in data ... else this formula wont work)

=IFERROR(VLOOKUP($D9,INDIRECT("A1:C"&COUNTA($A$1:$A$200)),2,0),0)

change the underlined number according to max length of data...

for Excel 2003 the formulae are:

=IF(ISERROR(VLOOKUP($D9,$A:$C,2,0)),0,VLOOKUP($D9,$A:$C,2,0))

and

=IF(ISERROR(VLOOKUP($D9,INDIRECT("A1:C"&COUNTA($A$1:$A$200)),2,0)),0,VLOOKUP($D9,INDIRECT("A1:C"&COUNTA($A$1:$A$200)),2,0))



Hope this Helps