issue with lookup table

[shin0bi272]

I have the following function in a cell
=UPPER(IF(H4="p1",LOOKUP(LEFT(O4,FIND("-",O4,1)-1),Sheet1!E:E,Sheet1!F:F),IF(H4="p2",LOOKUP(LEFT(O4,FIND("-",O4,1)-1),Sheet1!H:H,Sheet1!I:I),"")))

H4 contains p1
O4 contains aw07 p1-p1
Sheet1 column E contains values for AA07 P1 through AC07 P19
Sheet1 column E does NOT however contain values for aw07, thats Column H.

Sheet1 column F and I contain the values I want returned if a value is found in column E or H depending on what was put in h4 of the sheet with the cell Im working with.

So why is my formula returning a value instead of #n/a?

[Naveed Raza]

Hi

could u please provide sample workbook with desire output it will make us to give perfect solution !

[protonLeah]

To attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"