CODEWITHSUNDEEP
excel
user1566516
user1566516

Reputation: 1

Excel Cell Match - Copy other data from sheet

Basically I have a pretty simple sheet but I am struggling with it :(

Basically there are a few headings such as "Name", "Manager", "Credit"

I get a sheet that has a list of all Names etc and would like a macro to check if the Name is in the list and then to copy the credit cell from a sheet called "Agent" to a sheet called "Credit" have tried loads of solutions but to no avail.

Thanks

Upvotes: 0

Views: 5576

Answers (4)

Dan Norton
Dan Norton

Reputation: 362

When looking up LOOKUP vs VLOOKUP after reading lori_m's comment, I did not find anything regarding relative performance. Mostly I found that LOOKUP is only still in Excel for legacy compatibility and VLOOKUP or HLOOKUP are more flexible.

However, I did come across a series of articles starting at http://exceluser.com/blog/420/excel%E2%80%99s-vlookup-vs-index-match-functions.html which suggest that INDEX and MATCH functions provide an even better solution.

Upvotes: 1

Dan Norton
Dan Norton

Reputation: 362

The key function you need is VLOOKUP which takes the Lookup_value (The name to search for which can be a cell reference), the Table_array (The range to look in - It searches the first column of this range for the Lookup_value, returning the first alphabetical match or the first value past the Lookup_value so the list must be sorted and the results compared to the Lookup_value to see if they really match) and the Col_index_number (first column is 1 - it returns value from this column).

=IF(VLOOKUP(A2, Agent!$A:$B,1)=A2,VLOOKUP(A2, Agent!$A:$B,2),"No match")

This example will look up the value in A1 of the current sheet in the A column of the Agent sheet and return the value from the corresponding B column of the Agent sheet or "No match" if there isn't one.

Upvotes: 0

Reinier Torenbeek
Reinier Torenbeek

Reputation: 17383

Taking the Excel file that you shared as a reference, you could use the following provided that your first column on the sheet Agent is sorted in ascending order. In cell C2 of sheet Credit, enter

=LOOKUP($A2,Agent!$A:$A,Agent!$B:$B)

Then select that cell C2 and drag the lower-right corner down. This is the simplest version, but it will give strange results if a name from sheet Credit is not found on sheet Agent. A better version is

=IF($A2<>LOOKUP($A2,Agent!$A:$A,Agent!$A:$A),"Not Found",LOOKUP($A2,Agent!$A:$A,Agent!$B:$B))

The first part of the if checks to see if the Agent name is found on sheet Agent. If not, the result is "Not Found", otherwise the result is the same lookup as in the previous function.

Upvotes: 1

infinitloop
infinitloop

Reputation: 3011

Have you tried Lookup? This function finds a value in a single row or column and will do some matching against it in a different row or column.

Upvotes: 0

Related Questions