can a vlookup show a value based on if previous step is x or y

[dave beaven]

Help urgently required.

I created spreadsheet (see attached) and after doing all the work, someone decide extra data was needed
The spreadsheet is a selection chart for different product configuration, and indicates prices when certain configurations are shown.
My problem is i have a selection choice on worksheetCPB5800 CELL B23[information on worksheetCPS1]. This affects cost of measuring range of ROW23. [worksheetCPS3].
If I could program, I would expect CELL27 to have a function:
fx= IF B23=S, THEN E27=VLOOKUP(B27'CPS3'!A1:D5,3,FALSE, ELSE E27=VLOOKUP(B27'CPS3'!A1:D5,4,FALSE

(i.e. it chooses a different colum to collect data.)

Can anyone suggest a way I can achieve what I am looking for?

Thanks for everybodys help in advance

Dave

[rvasquez]

Hello,

This should be the correct formula.

B27=IF(B23="S",VLOOKUP(B27,'CPS3'!A1:D5,3,FALSE),VLOOKUP(B27,'CPS3'!A1:D5,4,FALSE))

Let me know if it works.

Thanks!

[dave beaven]

Hello
Thank you for your help. Unfortunately CELL E27 just displays TRUE, no matter what condition CELL B23 or CELL B27 are left in - now am even more confused - lol.
When someone selects B27, the cost of the selection (displayed in E27 based on WORKSHEET CPS3 ) is based on standard or premium which is defined in CELLB23
Have attached spreadsheet with your code implemented.


Thanks

Dave

[Down Under]

Best thing to do is on sheet CPS3, put in column headers in row 1 and label column c "S" and column d "P" (You don't need to name columns A and B).

Then just use this formula in E27 of your main sheet:

=VLOOKUP(B27,'CPS3'!A1:D6,MATCH('CPB5800'!B23,'CPS3'!A1:D1,0))

[dave beaven]

Thanks for reply

Did that, but got a #N/A.
I am not sure that using the present layout and structure will enable me to find a solution for this.
The MATCH function will only match one item.
I think whaat i want to do is when a certain value is selected, when i go to my next pick list, the O/P of this list is dervied(?) from the previous choice

Thanks for help


Dave