I have two lists with columns the same names and different numbers.
eg Peacetime 45 Cobar 60
Cobar 35 Peacetime 75
I would like the numerical values to line up with the Name
eg Peacetime 45 Peacetime 75
Cobar 35 Cobar 60
and then to sum the 2 numbers (I can do that manually once I get the matching list).
I have followed some VLOOKUP tutorials
and INDEX MATCH tutorial but I am getting an error #N/A
Thanks in advance for any help...
Doug
There are not 15 columns in the lookup range you have set (G to N = 8):
=VLOOKUP($F$2,'10212019MONRO'!$G$1:$N$169,15,FALSE)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali
I tried 15 but no luck.
I counted across and found Ratings was 14th column so tried 14 as well and still no good.
Thanks for your help.
I am telling you that 15 will not work because you only have 8 columns in your array. Which column in the range G to N are you wanting to return? Count from G to work out the number you need (where G is 1).
Try this in G2 [Ratings Lookup]:
=VLOOKUP($F$2,'10212019MONRO'!$M$1:$N$169,2,FALSE)
The other one for Rating Match, I am not clear what you are looking for.
Can you enter result manually and explain the logic?
1. Click on the * Add Reputation if you think this helped you
2. Mark your thread as SOLVED when question is resolved
Modytrane
As Ali tried to explain, you need to count total columns in the Array range. In your original post that would be columns G to N [8]. Then your formula can look for result in any of those columns [from 1 to 8], 1 being column G and 8 being column N.
You should be using this (note the removal of $ from the $F$2 lookup value and the column to look up, column M)
=VLOOKUP($F2,'10212019MONRO'!$M$1:$N$169,2,FALSE)
and copy down the column
More importantly column M on your MONRO sheet does NOT match the column F on your ratings Lookup sheet.
You have extra spaces in column M that do not appear in column F.
For a VLOOKUP to work the lookup value has to match the data on the sheet exactly, yours doesn't.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Thanks all for your patience - I'm a bit slow with this being so new to me.
I really appreciate your help and will try again and let you know.
Thanks again everybody.
Special K - the names in column N were different to column F because the source left out the ' punctuation mark.
Funny how it didnt correct when I fixed the errors but there were only a few so it was ok to enter the rating manually.
I understood the counting to enter the single digit in the formula so thanks for that too.
So problem solved.
Hoping I can make it work in a new task.
I only tried the INDEX MATCH because I couldn't get the VLOOKUP to work.
All thanks and reps added.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks