Help with a simple problem

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi Guys,

Really hope someone can help me out with this problem as it's driving me
crazy!

On Sheet 1, I have a list of Company Names in column 'A' and their Postcodes
in column 'B' I have a lot of other data in columns 'D' to 'H', however I
want to use column 'C' in Sheet 1 to input a member of staffs name next to
the company name and postcode.

Sheet 2 contains a master list which has a list of all members of staff in
column 'A', most of the company names (but not all of them) in column 'B' and
their postcodes in column 'C'

Is there a way to get Excel to go through each company name and postcode on
Sheet 1 and check if exists on Sheet 2, and if there is a match to grab the
staff members name and put into column 'C' on Sheet 1.

There is a possibilty that the company name and postcode on Sheet 1 isn't
contained within the master list on Sheet 2 and therefore wouldn't be a staff
members name to input in column 'C' on Sheet 1, in this instance I would like
it just to say "???"

I've tried a VLOOKUP formula, however it's not very reliable as if the
company dosn't exist in the master list, then EXCEL just seems to make it's
own mind up what to put in.

Hope you can help,
Many thanks,
Dave
 
You wrote: "then EXCEL just seems to make it's own mind up what to put in"
Read in Help about the last argument in VLOOKUP which determines if a exact
match if needed or if something close will do. Clearly you need an exact
match
best wishes
 
Hi,

A number of issues here. First do you know the Staff Member's name if you
know the Company name, or do you need the Zip Code also? Second issue is
that VLOOKUP is designed to look at the left most column of a lookup range
(table) and your discription suggests that the lookup must be on Company name
which is in the second column.

1. If the first is the case then you can use LOOKUP as follows:
=LOOKUP(A1,Sheet2!$B$1:$B$4,Sheet2!$A$1:$A$4)
When you use LOOKUP the list you are looking at must be sorted in Ascending
order, in you example, that means the company names in Sheet2. In the above
example A1 contains a company name on sheet1 which we want to get the staff
member's name for. B1:B4 contain the company names on sheet2 and A1:A4
contain the staff names.
2. In case two you are asking to do a multicolumn lookup, that means both
the company name and the postal code must match. There are many ways to do
this, here is just one:

=INDEX(Sheet2!$A$1:$A$4,MATCH(1,(Sheet1!A1=Sheet2!$B$1:$B$4)*(Sheet1!B1=Sheet2!$C$1:$C$4),0))

This formula needs to be array entered: Press Shift+Ctrl+Enter instead of
enter.

3. Both of the above formulas will return errors if no match is found,
however, the type of error depends on exactly what is wrong. Sometimes the
answer will be incorrect, in other cases one of Excel's error messages may
appear, such as #N/A.

Here is one way to handle the second formula's issue
=IF(ISNA(MATCH(1,(Sheet1!A1=Sheet2!$B$1:$B$4)*(Sheet1!B1=Sheet2!$C$1:$C$4),0)),"???",INDEX(Sheet2!$A$1:$A$4,MATCH(1,(Sheet1!A1=Sheet2!$B$1:$B$4)*(Sheet1!B1=Sheet2!$C$1:$C$4),0)))

In the first case you would probably want to modify the formula entirely to
read something like
=IF(ISNA(MATCH(A1,Sheet2!$B$1:$B$4,0)),"???",INDEX(Sheet2!$A$1:$A$4,MATCH(A1,Sheet2!$B$1:$B$4,0)))
 
Back
Top