I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
contain a store number. stores.xls also has gross sales listed in column F.

The formula will reside in sales.xls (column G for each row). What I need to
do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
it finds a match, I need it to return cell F [stores.xls] in the same row.

Because there are some cells that will be a null value, I need them to
return blank.

I was thinking that this would be a nested IF statement with a VLOOKUP, but
I wasn't sure if that was efficient or the only way. Please help if you have
any ideas.

Thanks!

hi Plunk,

=if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0))

hth
regards from Brazil



"plunk25" escreveu:

I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
contain a store number. stores.xls also has gross sales listed in column F.

The formula will reside in sales.xls (column G for each row). What I need to
do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
it finds a match, I need it to return cell F [stores.xls] in the same row.

Because there are some cells that will be a null value, I need them to
return blank.

I was thinking that this would be a nested IF statement with a VLOOKUP, but
I wasn't sure if that was efficient or the only way. Please help if you have
any ideas.

Thanks!

Hi,

This might help you:

=IF(ISERROR(VLOOKUP(.....)),"",VLOOKUP(.....))

If the function has some error in it, if will return an empty cell
otherwise the corresponding value for the VLOOKUPed value.

Thanks.

Shail




plunk25 wrote:

I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
contain a store number. stores.xls also has gross sales listed in column F.

The formula will reside in sales.xls (column G for each row). What I need to
do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
it finds a match, I need it to return cell F [stores.xls] in the same row.

Because there are some cells that will be a null value, I need them to
return blank.

I was thinking that this would be a nested IF statement with a VLOOKUP, but
I wasn't sure if that was efficient or the only way. Please help if you have
any ideas.

Thanks!

=IF(ISNA(VLOOKUP(A2,[stores.xls]Sheet1!$C$1000:$F$1000,4,FALSE)),"",VLOOKUP(
A2,[stores.xls]Sheet1!$C$1000:$F$1000,4,FALSE))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"plunk25" wrote in message
...
I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column
that
contain a store number. stores.xls also has gross sales listed in column
F.

The formula will reside in sales.xls (column G for each row). What I need
to
do is compare [sales.xls]A2 to column C in stores.xls and find a match.
After
it finds a match, I need it to return cell F [stores.xls] in the same row.

Because there are some cells that will be a null value, I need them to
return blank.

I was thinking that this would be a nested IF statement with a VLOOKUP,
but
I wasn't sure if that was efficient or the only way. Please help if you
have
any ideas.

Thanks!

Marcelo - I believe your vlookup's column index number should be 4:

=if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,4,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,4,0))

--
Regards,
Dave


"Marcelo" wrote:

hi Plunk,

=if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0))

hth
regards from Brazil



"plunk25" escreveu:

I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
contain a store number. stores.xls also has gross sales listed in column F.

The formula will reside in sales.xls (column G for each row). What I need to
do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
it finds a match, I need it to return cell F [stores.xls] in the same row.

Because there are some cells that will be a null value, I need them to
return blank.

I was thinking that this would be a nested IF statement with a VLOOKUP, but
I wasn't sure if that was efficient or the only way. Please help if you have
any ideas.

Thanks!

yeah David, my mistake

regards


"David Billigmeier" escreveu:

Marcelo - I believe your vlookup's column index number should be 4:

=if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,4,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,4,0))

--
Regards,
Dave


"Marcelo" wrote:

hi Plunk,

=if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0))

hth
regards from Brazil



"plunk25" escreveu:

I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
contain a store number. stores.xls also has gross sales listed in column F.

The formula will reside in sales.xls (column G for each row). What I need to
do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
it finds a match, I need it to return cell F [stores.xls] in the same row.

Because there are some cells that will be a null value, I need them to
return blank.

I was thinking that this would be a nested IF statement with a VLOOKUP, but
I wasn't sure if that was efficient or the only way. Please help if you have
any ideas.

Thanks!