Vlookup from another sheet to copy formula number

tchav

Hello,

I am trying to determine how to get a cell copied into a sheet from another sheet based on certain criteria using a vlookup formula.

Here is the sheet i would want the formula to autofill in. When Gunma (Asahi) and Aussie lemonade are chosen, I would like the formula number to autofill pulling from another sheet.

So the formula would need to Vlookup into the sheet below and find the formula number based on the product "aussie" and Copacker "Asahi"

What formula would i enter in the formula number column to make it search the second sheet to find the correct formula number?

Leibel S

@tchav

Try the below:

=INDEX(COLLECT({Formula},{Product},CONTAINS(@cell,Product@row),{copacker},CONTAINS(@cell,Plant@row)),1)

Make sure to update the bolded cross sheet references

tchav

I did this and it still isnt working

=INDEX(COLLECT({HBC Master File by Site 20230324 Range 4}, {HBC Master File by Site 20230324 Range 2}, CONTAINS(@cell, Product@row), {HBC Master File by Site 20230324 Range 3}, CONTAINS(@cell, Plant@row)), 1)

@Leibel S

Paul Newcome

Try switching the arguments around in both of the CONTAINS functions.

CONTAINS(Product@row, @cell)

tchav

@Paul Newcome @Leibel S It worked finally after some trial and error. Thank you for your help.

One more question:

What would I put in the formula to give an error if there are multiple of the same product listed in the sheet? Sometimes we have the same products with multiple different formulas and right now it is grabbing the first formula. I want there to be an error if there are multiple different formulas for the same product.

=INDEX(COLLECT({HBC Master File by Site 20230324 Range 1}, {HBC Master File by Site 20230324 Range 2}, CONTAINS(@cell, Product@row), {HBC Master File by Site 20230324 Range 3}, CONTAINS(@cell, Plant@row)), 1)

Paul Newcome

You would need to use an IF/COUNTIFS combo. Basically you would use a COUNTIFS to count how many rows there are matching the same range/criteria sets you have in your COLLECT (not including the very first range) then say IF that is greater than 1 then output "this error", otherwise run the INDEX/COLLECT.

=IF(COUNTIFS(..........)> 1, "error message", INDEX/COLLECT)