Can I put a variable row number in an IF function???

I'm creating a costing sheet for cocktails with separate sheets labelled "CALCULATOR" and "Liquor" and I am trying to make the CALCULATOR automatically populate certain information from the Liquor sheet. Here is my problem...

The relevant information on the Liquor sheet is in columns A, the product name, and E, the cost per ounce (E3 = cost per ounce of A3, the same is true for all rows in columns A & E). On the CALCULATOR sheet, I've created a drop-down list of the liquor options (Liquor!A) and I would like the CALCULATOR sheet to automatically populate the cost per ounce section (CALCULATOR!F)when the corresponding alcohol is selected.

On the CALCULATOR sheet, the simple formula that works for a single row is =IF(D6=Liquor!A3,Liquor!E3) but I can't figure out how to make the row number variable or how to make the row number of E dependant upon and matching that of column A.

I hope this explanation is comprehensive enough to understand my problem. Any help would be greatly appreciated. Normally I can find answers on google, but this particular search has repeatedly come up empty.

Hi Nate, welcome to the forum. Sounds like you need to use a lookup formula. INDEX/MATCH is the most flexible approach. Something like:

Please Login or Register  to view this content.

This looks in LiquorA for the value in D6 ("match"). If found, it returns the corresponding value from LiquorE ("index"). One warning: if D6 value appears more than once in LiquorA, this only finds the FIRST instance. You'll need to add additional criteria in order to differentiate.

MATCH(lookup_value, lookup_array,[match_type]) The zero is the match_type code:

0 = exact match. lookup_array may be unsorted.
1 = approximate match. Finds the largest value less than/equal to. lookup_array must be sorted small to large.
-1 = approximate match. Finds the smallest value greater than or equal to. lookup_array must be sorted large to small.

Here's a decent intro to these and other functions: https://exceljet.net/index-and-match

Also, thank you for the rep! If satisfied, please go to Thread Tools (up top) and mark your thread as SOLVED. Regards - Lee

This is your formula in 'Master Calculator'!E7 :

Please Login or Register  to view this content.

The formula is referring to itself, a no-no in Excel known as a circular reference. The value you're trying to match - "Smirnoff 750" - is in cell D7. So change E7 to D7:

Please Login or Register  to view this content.