Hello all,
I am doing a Vlookup between two excel files. The two files both have an account number column and a surname column which is the same. I have setup a few vlookups which look up the account number of a customer and then bring in the address fields (separate vlookups for each address field).
The data is very sensitive regarding account information, so for that reason I would like to add to the lookup functions another check, that checks the surnames match as well before the address field is moved across.
I imagine I would have to somehow return the cell reference of the surname in the second file (as it could be anywhere depending on what row the account number is in) after the vlookup is done and then use the exact function to check if it matches the surname in the first file.
Any help with this is much appreciated.
Kind regards, Mike
Why not concatenate # & Name into a single field and then base subsequent calculations on the location of the matching concatenation ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi there,
Thanks for your quick response.
I'm not too sure I follow what you mean!
It's hard to give specific examples with no range information but you mention you have account number column and surname column and you wish to conduct matches based on both fields (not just account #)
If you concatenate the two columns into a new single column you can search based on the concatenation
For sake of simplicity I will use a single workbook example - 2 sheets - Sheet1 & Sheet2
Sheet1 Col A has #, B has Name
Sheet2 Col A has #, B has Name and C has address
Sheet1 Col C is to retrieve the appropriate Address value from Sheet2 for the # & Name combination as determined by values in A & B on Sheet1
We can add a column to Sheet2
We can then use the above in our Calculations on Sheet1Sheet2!D1: =A1&"@"&B1 copied down for all of our data
In short the concatenation on Sheet2 allows to conduct "multi criteria" searches using a single criteria.Sheet1!C1: =INDEX(Sheet2!$C:$C,MATCH($A1&"@"&$B1,Sheet2!$D:$D,0)) copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Brilliant. Thank you very much!
I was trying to build some complex formula and this solves it.
Thanks once again!
Kind regards, Mike
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks