Isna match vlookup

[EXCEL_Newbie87]

Im pretty new to excel / Ive been away for a long time. So please be kind with what is probably an obvious problem. Im using ISNA MATCH from one worksheet named "Bonnet" (as in under the bonnet - to keep important things away from others that will be using this spreadsheet) to return values to Sheet one. There are two tables of values which would need to be searched dependant on the selection of a list from column H in this case. I have a feeling I may have to attach the file in order to be understood fully.. But

=IF(ISNA(MATCH($J$1,Bonnet!$H$3:$L$3,0)),0,INDEX(Bonnet!$H$4:$L$15,MATCH([Area],Bonnet!$F$4:$F$15,1),MATCH($J$1,Bonnet!$H$3:$L$3,0)))

thats the ISNA MATCH, Ive tried to add an additional IF STATEMENT To try to determine which table of values will be searched and return a value, but I cant quite seem to get it to work...Any help with the syntax or if using another IF statement is wise or not, would be great -

 

[NBVC]

Can you supply the information for what the condition is. Ie, what are the 2 options made, and what tables are searched for each option?

 

[EXCEL_Newbie87]

The options are strings from a list within a cell - "Yes" or "No" and they related to different tables of values - so if its Yes than Table A should be searched and if its No then the other table should be searched -the values within the tables are different but all other criteria are the same - the idea of the spreadsheet is to use as a pricing template for work.. I would attach the file but I dont seem to see an option to do so..

 

[NBVC]

I am thinking something like:

=IF(X1="Yes",INDEX(Bonnet!$H$4:$L$15,MATCH([Area],Bonnet!$F$4:$F$15,1),MATCH($J$1,Bonnet!$H$3:$L$3,0)),INDEX(table2,MATCH([Area],table2[column1],1),MATCH($J$1,table2[row1],0)))

where you would fill in the table2 info in the 2nd INDEX/MATCH for when "No" is picked.

 

[EXCEL_Newbie87]

Sorry you ve lost me a bit with the MATCH([Area]) bits

=IF(Toughened="Yes",INDEX(Bonnet!$H$4:$L$15,MATCH([Area],Bonnet!$F$4:$F$15,1),MATCH($J$1,Bonnet!$H$3:$L$3,0)),INDEX(Bonnet!$H$21:$L$32,MATCH([Area],Bonnet!$F$21:$F$32,1),MATCH($J$1,Bonnet!$H$20:$L$20,0)))

I think the range for the second table is correct but Im not sure what is meant to go in the MATCH([Area] bits...

 

[NBVC]

I took that from your original formula... maybe I am confused to.... what did you mean by [Area]. I assumed that is the lookup value for the row in the table

 

[EXCEL_Newbie87]

My mistake! Too many file versions open! - I forgot to add some [ ] into part of the formula and it wasnt working and I had forgotten that I had named one of the columns as Area - Thanks for the help!