Log In
Register
Home
Dashboards
Power Pivot
Power BI
August 16, 2018
Offline
Hi, I have 2 high excel list of information where i need to use VLOOKUP formula. I have tried multiple times and it always give "N/A". So i decided to write to you instead. Maybe VLOOKUP might not be a good idea for t he scenario below. I have over 2K of entries, and doing ,manual search is going to be very time consuming.
Excel A: Is the main inventory list where i need to input the information based on 2 columns criteria and whole range. Return value should be from Excel B
Excel B: Is where i need 2 columns to match the Excel A columns. And send the value from another column to Excel A.
See below explantion of the flow needed:
Excel A:
Columns A&B- Country Name(Spelled out in full) and Code (8 digits)
Columns P&Q- License number(mixture of alphabets and numbers) and Date(needs to be in dd/mm/yyy format)
Excel B:
Columns D&E- Country Name (in 2 Alphabets) and code (8 digits)
Columns S&T- License number(mixture of alphabets and numbers) and Date(needs to be in dd/mm/yyy format)
On excel A, i need to VLOOKUP to excel B. Columns AB must match to DE if yes, columns S&T information must be the return value from Excel B to excel A Columns P&Q. Else, i would like a "N.A." to return with cell highlighted as red.
Is above possible using VLOOKUP? Kindly help and advise please. If not what other ways or formula can i use? Thank you so much.
Thanks and regards,
Firah
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Firah
It would help if you could post a few sample in a file for us to see.
From your description, it looks like the problem is the Country name as one is in full and another is 2 character.
Sunny
Dashboards
Power Pivot
Power BI
August 16, 2018
Offline
Hi, Please see attached files and guide below on what i actually need help for. Apologies if above caused confusion.
Excel A:
Columns A&B- Country Name(Spelled out in full) and Code (8 digits)
Columns D&E- License number(mixture of alphabets and numbers) and Date(needs to be in dd/mm/yyy format)
Excel B:
Columns D&E- Country Name (in 2 Alphabets) and code (8 digits)
Columns S&T- License number(mixture of alphabets and numbers) and Date(needs to be in dd/mm/yyy format)
On excel A, i need to VLOOKUP to excel B. Columns A&B of excel A must match to columns D&E of excel B if yes, columns S&T of excel B information must be the return value to excel A Columns D&E. Else, i would like a "N.A." to return with cell highlighted as red.
VIP
Trusted Members
June 25, 2016
Offline
Hi Firah
It is not so straight forward as both file lookup value must exactly match for the VLOOKUP to work.
You will have to first make sure that the country for both files are the same. I have converted the full country name to 2 characters using a table.
I have also created some helper columns. I have also used Conditional Formatting to highlight the N/A.
Good luck
Sunny
Answers Post
1 Guest(s)
