How to nest vlookup into ifs function?

I am trying to get a return value in cell E2 when a value of 1,2 or 3 is entered into cell D2. I am trying to use the "IFS" function to grab a Here is a snip of this table.

Capture.PNG

I fetched the value in cell E2 from the the table below.

Capture 3.PNG

I am trying to embed vlookup into the ifs function to get the appropriate cost/unit rate when 1, 2 or 3 is entered into cell D2

The vlookup is =VLOOKUP(B2,'Cost Data'!$B$3:$J$40,6,FALSE)

Please help.

Thanks
- Mike

IFS is not the right tool for this. What you are trying to do is match a row based on values in two columns.

Formula:

Please Login or Register  to view this content.

This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

Attach the workbook for troubleshooting.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

No file attached - please follow the instructions in my previous post.

Thank you.

Is this what you want?

=INDEX('Cost Data'!$G$3:$G$40,MATCH(B2,'Cost Data'!$B$3:$B$40,0))*D2

Ordinary formula.

No problem.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I believe that the issue is two fold.
1) Use Jeff's array entered formula from post #10 (modifying so that it is matching the Area Item in A7 and No. of Coats in D7)
2) In File > Options > Formulas > Calculation options, make sure that Automatic is selected (Manual is selected when file attached to post #15 is opened).
Let us know if you have any questions.

if you are asking if you must use the first column for a vlookup to work, the answer is no. BUT, whatever column you choose to base your lookup value on , must also be the first column of the lookup array.
if youre asking with regard to having duplicate values, then it is always wise to use a column with distinct values as your lookup or lookup array.

so if works has unique values for all products that in the future will never be duplicated for say, a different product. i would go with the works column.
it would act as a barcode (primary key).
you can also concatenate the works column with the description column giving a 2 column key.

i hope this helps

are you still multiplying this result(cost data, column G ) by the number of coats selected on the Recap sheet column D?
alternatively, do you only want to lookup what the hrs/unit in column G of cost data sheet and ensure it aligns with the number of coats chosen?

multiply labor rate * no of coats (exsisting formula in workbook you just sent)

Formula:

Please Login or Register  to view this content.

if its just a lookup of the labor rate dependent on no of coats chosen (same formula as above - the "*$D9" at the end). it also does your if check.


=IF($A$7:$A$200="",0,INDEX('Cost Data'!$G$3:$G$109,MATCH($A9&$D9,INDEX('Cost Data'!$B$3:$B$43&'Cost Data'!$C$3:$C$86,),0)))

ps. also note the concatination i mentioned earlier in the above function. MATCH($A9&$D9. I joined the area item and the no. of coats to ensure referential integrity of formula.
hope this helps