vlookup question

[Guest]

Hi All,

I asked this question previously but perhaps did not explain it too well, I
will try again.

I have two different spreadsheets, A & B in excel 2007 and am trying to
compare like Customer ID Numbers between the two spreadsheets.

Spreadsheet A is a weekly report of sales and has the following columns:

Cust ID, Cust Name, Sales Order #, Date, Status, Sales Amount & Sales Rep.
the length always changes from week to week, but the columns remain the same.

Spreadsheet B is a running report of customers that our telemarketer has
brought into our store. It has many columns, but one of which is the CustID,
just like in the previous Spreadsheet A.

I would like to run a formula, in Spreadsheet A that will take the Cust ID
on each row in Spreadsheet A and compare it to the list of those Cust ID's in
Spreadsheet B. If the Cust ID is not found, do nothing. If the Cust ID is
found - I would then like to have the Sales Amount value brought in as the
result.

Column ID's in Spreadsheet A:

1st Column (A) is Cust ID
6th Column (F) is Sales Amount
9th Column (I) is where I would like this formula (and result if the Cust ID
is on the other spreadsheet) to go.

Column ID in Spreadsheet B:

Cust ID is in column H and will either have a four digit numerical number
representing the Customer ID Number, or it will be blank. I can reformat the
number to be text if that will help in the lookup.

Thanks for your help, if you have any questions, or if I can clarify the
example better, please let me know.

 

[Guest]

Hi,

Not sure if this is what you're looking for but maybe:

=IF(COUNTIF(Sheet2!$H$1:$H$1000,A1)>0,F1,"")

adjust to suit and copy down as far as needed.

HTH
Jean-Guy

 

[Roger Govier]

Hi Mark

If I have understood you correctly then
=IFERROR(VLOOKUP($A2,SheetB!$H:$H,1,0)),"",F2)

 

[Guest]

=IF(ISNA(VLOOKUP([Book2]Sheet1!H2,$A$2:$F$6,6,FALSE))=TRUE,"
",VLOOKUP([Book2]Sheet1!H2,$A$2:$F$6,6,FALSE))

This formula seemed to work when I did a little test. Here is what it does:

It looks in Book2, Sheet 1(Your spreadsheet B) at H2 which is the Cust ID.
It then looks at the range of all of your data in spreadsheet A and if it
finds an exact match it takes the data from the 6th column over (which should
be the sales amount) and displays it. (The FALSE part tells it to look for
an exact ID match.)

The formula above is essentially liste twice becasue the 1st part is looking
to see if the result is N/A, if it is it will display blank (This means if
could not find a cust id), if it does find a valid cust id it will show the
result.

Does this make sense?