=IF(D14='geo capacity'!A1,VLOOKUP(D15,'geo capacity'!A11050,2,IF(D14='geo capacity'!K1,VLOOKUP(D15,'geo capacity'!K2:O1050,2,IF(D14='geo capacity'!F1,VLOOKUP('geo capacity'!F2:H1050,2,FALSE))))))



What I am trying to do is look up piles in a table depending on the geotechnical capacity of the soil. The above formula only returns a product if the code in D14 is the first look up in the table. If not it returns false. What I want to do is insert the soil code and then be able to look up the piles with the corresponding soil code in the table.
The table consists of 3 soil types in each of cells A1, F1 and K1 with the appropriate piles listed under each soil type.

Yoiu seem to be missing some parentheses

=IF(D14='geo capacity'!A1,VLOOKUP(D15,'geo capacity'!A11050,2,0),IF(D14='geo capacity'!K1,VLOOKUP(D15,'geo capacity'!K2:O1050,2,0),IF(D14='geo capacity'!F1,VLOOKUP('geo capacity'!F2:H1050,2,0),"")))

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Without an example workbook to look at, it's to much like hard work to visualise what you have, so I came up with my own idea.
Have a look at the attached example to see if you can (or want to) adapt it to your needs.
Cheers

3rd attempt to attach a file

Attached Files

Piles.xlsx (11.3 KB, 10 views)

That worked. Thank you very much for your help.