Software

Ask something
Discussion Locked

Excel formula

0 helpful
5 replies

by hdahman

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have two excel spreadsheets that contain company names. One of the spreadsheets also contains the company’s account number. How do I do a formula that would compare the companies in spreadsheet a, column d to the companies in spreadsheet B,column B and if they are the same return the account number from column D of spreadsheet B into column F of spreadsheet A?

5 replies

by kees.valkenswaard

You would have to use the VLOOKUP function. E.g. In F3 the following formula would work:
=VLOOKUP(D3,mylist,3,FALSE)
However, it is not recommended to apply a Vlookup directly on an array in another file. So, first make in the same file, maybe on another page, a link to the array in the other workbook. This is copy, paste special.., paste link. Give the array the range name mylist. [The array would include at least 3 columns and the names are in the left most one]
More elaborate would be:
=if(iserror(VLOOKUP(D3,mylist,3,FALSE)),”no # found”, VLOOKUP(D3,mylist,3,FALSE))

Note your list separator might not be , but ;

Good luck,

by dreamboat

To use the previously described answer, you must first select the columns on worksheet B and hit Insert-Name-Define and type in "MyList". This is called naming a range or named range. You will then be able to use the formula in cell F3 of Sheet A:
=VLOOKUP(D2,MyList,3,FALSE)

Where:
D2 contains the account number on Sheet A
MyList is the named range you applied by selecting columns B through D of Sheet B.
3 is the number of the column of the named range you want to return
FALSE means do not find the nearest value to your account number, but an exact match

Hope that helps!

This discussion is closed to new replies.