Forum Discussion
Searched for videos, tutorials, etc as I might, I can't find the answer.
Me again. The first time I tried it said SPILL. I got REF and N/A. Then at one point I was getting the row number.
I used VLOOKUP to infill the column. It worked. I can merge P & Q now but column AI used column P so I'll have to jig something up for it. VLOOKUP doesn't look left so.....
Otto_Rayne Since you had a #SPILL! error you are on a modern version of Excel and you probably have access to the new XLOOKUP function. That function might help you solve your problem. But, INDEX and MATCH should also be able to do the trick. If you still can't get it to work, please upload an example of your workbook (the real thing, stripped from any confidential information). Then it will be much easier to come-up with a solution.
Riny_van_EekelenExcellent. Thanks. I used the XLOOKUP. It worked. Unfortunately I realized I can't delete the extra column because it is how I sort it. The values ending in aa, gg, etc end up at the bottom. These are the 2 forumlas I used for the respective columns.
=XLOOKUP([@Distance], PP!$B$3:$B$55, PP!$C$3:$C$55)
=IF([@Distance]=1,PP!$E$3, IF([@Distance]=2,PP!$E$4,IF([@Distance]=3,PP!$E$5,IF([@Distance]=4,PP!$E$6, IF([@Distance]=5, PP!$E$7, XLOOKUP([@Distance], PP!$B$8:$B$55,PP!$A$8:$A$55))))))
I've learned a lot more these couple of days. I keep learning new formulas and hacks. I love it. Thanks for the suggestion.
Otto_Rayne You're welcome!
