Looking Up Values

[mlbdc2012]

I'm trying to come up with a way to lookup a value based on criteria entered in a certain cell on another sheet. I can't seem to figure it out for the life of me. I'm sure it's something really easy.

I have three tabs in my worksheet... The lookup would be located on the 'Final Value' tab in column C. That value would be based on the criteria entered on the 'Main' tab in cell D8. If '260' was selected in cell D8, the value for cell C3 of the 'Mixed Value' tab would be $41 based on the criteria from cell E6 of the 'Value Database' tab.

On the otherhand, if '290' was selected in cell D8 of the 'Main' tab, the value for cell C3 of the 'Mixed Value' tab would be $43 based on the criteria from cell L6 of the 'Value Database' tab.

Can someone please help me with this? Much appreciated. My worksheet is attached. Thanks!

[yay_excel]

This data is set up in a very confusing way... Are your only options 260 and 290? That is how it currently appears to me.

[mlbdc2012]

This data is set up in a very confusing way... Are your only options 260 and 290? That is how it currently appears to me.

For now yes, but I will be adding more choices later. Does that matter? I wanted to simplify things for the example

[yay_excel]

Yes, it does matter. First of all, in order to effectively use lookup formulas your data must be formatted consistently and in order. This means that we need to have a table somewhere which contains all of the possible data we will be looking up, both the lookup value and the return value. There should not be any gaps in the data, meaning no blank rows or columns. The labels must be in order (alphabetical or numerical) in either the first row or the first column of the data. Basically, if we keep your labels in the first row and use the data you have provided so far, we should have a table with the labels PLAYERS DRAFTED and RANK 1 in A1:A2 and the corresponding data in cells B2:C3. See new worksheet added to your file; it contains the data we need in a usable format. Let me know if you need help reformatting the data.

[mlbdc2012]

Yes, it does matter. First of all, in order to effectively use lookup formulas your data must be formatted consistently and in order. This means that we need to have a table somewhere which contains all of the possible data we will be looking up, both the lookup value and the return value. There should not be any gaps in the data, meaning no blank rows or columns. The labels must be in order (alphabetical or numerical) in either the first row or the first column of the data. Basically, if we keep your labels in the first row and use the data you have provided so far, we should have a table with the labels PLAYERS DRAFTED and RANK 1 in A1:A2 and the corresponding data in cells B2:C3. See new worksheet added to your file; it contains the data we need in a usable format. Let me know if you need help reformatting the data.

Ok I've updated the data and reattached the worksheet.

[yay_excel]

I have added an HLOOKUP formula which I believe does what you have requested. Because the player rank values are in the same order on the final value worksheet as they are in the database, I did not write the formula to look for the matching rank number in the database. Instead, it uses the the data from the corresponding row.

[mlbdc2012]

I have added an HLOOKUP formula which I believe does what you have requested. Because the player rank values are in the same order on the final value worksheet as they are in the database, I did not write the formula to look for the matching rank number in the database. Instead, it uses the the data from the corresponding row.

Awesome! Thanks

[mlbdc2012]

I have added an HLOOKUP formula which I believe does what you have requested. Because the player rank values are in the same order on the final value worksheet as they are in the database, I did not write the formula to look for the matching rank number in the database. Instead, it uses the the data from the corresponding row.

What if I have 3 columns with the same number of players drafted? How will it know which column to look up?