I went to Sheet 2 and changed the prices using both of your formulas, but
both seem to work fine giving the same results. Not sure what the new formula
does different.

PS
If anyone can answer my 2 new questions I'd appreciate it, if not, I will
try posting it as a new question. Thanks!
--
In God''s Harmony


"MyVeryOwnSelf" wrote:

One way is to start by putting the above price table in Sheet2!A:D.

To fill in the third column of Sheet1, put this in Sheet1!C2 and copy
down:
=VLOOKUP(A2,Sheet2!A:D,
IF(B2="Regular",2,IF(B2="Medicare",3,IF(B2="Cash", 4,5))),
FALSE)
The idea of the IF() is to select a column of Sheet2 to use based on
what's in Sheet1!B2.

Here's a different formula for Sheet1!C2 I like better:
=OFFSET(Sheet2!$A$1,
MATCH(A2,Sheet2!$A:$A,0)-1,
MATCH(B2,Sheet2!$1:$1,0)-1)
It's more general. The payment types on Sheet2 can change without having to
change the formulas on Sheet1.