Searched for videos, tutorials, etc as I might, I can't find the answer.

[Otto_Rayne]

I have a Customer Master list where they are labelled with a region in column P, subregion column Q.  Pricing is based on the column P, pulling the prices from a separate sheet using IF(), unless there is a value in the subregion column Q.  If Q has a value, I have to manually enter it in because I can not figure out an IF or MATCH or INDEX formula that will allow me to, based on the value, search the next sheet for data in the column next to the matching value.  Cell Q10 for example, is subregion 1aa.  I would like it to pull the information from Sheet 2, B8 (1aa) and enter the value from C8 into Sheet 1, AK10 ($price). Q80 is 2dd, so AK80 will read ($price) from Sheet 2.  Etc.

I tried a bunch of MATCH & INDEX formats.  I have six IF variables so far for the regular regions but in total there are 50 price variables with the subregions.  PLEASE HELP!

[#2]

@Otto_Rayne

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. 

[#3]

@Otto_Rayne 

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.....

[#4]

@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.

[#5]

@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.

[#6]

@Otto_Rayne You're welcome!