multiple vlook ups help!!!!

Hi I need a forumla that does the following please.

If cell P2 = N/A then vlook upworksheet 2, d18:E32, return 2nd col, if cell P2 does not have N/A, then vlook up worksheet 2, d2, e18 return the 2nd col.

Any ideas how to do this please?

Thanks

Try this...

=IF(P2="N/A",VLOOKUP(P2,Sheet2!D18:E32,2,0),(VLOOKUP(P2,Sheet2!D2:E17,2,0)))

Thanks. Sadly that doesn't work

Not sure whats the problem....could you please upload sample workbook?

Apologies it does half work. I missed out one part that is why it isn't working 100%.

It should be that if If cell P2 = N/A then vlook up cell e2,worksheet 2, d18:E32, return 2nd col, if cell P2 does not have N/A, then vlook up cell p2 in worksheet 2, d2, e18 return the 2nd col.

oh!...I think below will do

=IF(P2="N/A",VLOOKUP(E2,Sheet2!$D$18:$E$32,2,0),(VLOOKUP(P2,Sheet2!$D$2:$E$17,2,0)))

If NA then lookup in range D18 to E32
If not NA then lookup in range D2 to E17

Thanks. It doesn't like the N/A part, if I change that to something else like b, then it seems to work

Yes...it will work.

If you are satisfied with the answer, could you please mark the thread as solved.