I have several columns with different prices for my location codes. I want to use a certain column's price based on the shipping amount. For example, if my amount is less than 100 I would want to use column B's price in the attached spreadsheet. If the amount is greater than 20,000 then I want to use column K. If the amount is 3,500 then I would use column G. How do I do this? I imagine it is some sort of Index Match or a conditional formula in the lookup column of the vlookup. I may have more conditions then the attached example, so I think "IF" is limited to 7 nested formulas so that wouldnt scale. Thanks in advance.
Conditional-Column-Lookup.xlsx
=VLOOKUP("ATLA",A$2:K$300,
where "ATLA" is the lookup value and 3500 the amount. Obviously you can use cell references for those values e.g. like
=VLOOKUP(M2,A$2:K$300,MATC
where M2 and N2 contain the lookup values
regards, barry