select value in table based on values entered in multiple cells
I'm trying to find a formula that will verify the value in many cells within
a row and select a value in a table based on the values entered for those cells within the row. in one column, I indicate if the piece of wood in 4/4 or 5/4 in another column, I indicate the type of wood and finally in another column, I indicate the quality of wood. Based on these 3 criterias, I would like to find the appropriate price (within a table created on a separate tab) based on these criterias. I'm a bit new to the usage of formulas and can't find the appropriate formula to use |
select value in table based on values entered in multiple cells
This is how I would do it:
table layout: A B C D E Size Type Quality SizeTypeQuality Price The formula in column d: =a1&b1&c1 Copy Down So in another worksheet: A B C D2 1 Size Type Quality Price 2 Enter the variables in row 2 The formula in d2: =vlookup(a2&b2&c2,'Table'!D1:E12,2,false) |
select value in table based on values entered in multiple cells
Maybe...
Saved from a previous post: If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) claudard64 wrote: I'm trying to find a formula that will verify the value in many cells within a row and select a value in a table based on the values entered for those cells within the row. in one column, I indicate if the piece of wood in 4/4 or 5/4 in another column, I indicate the type of wood and finally in another column, I indicate the quality of wood. Based on these 3 criterias, I would like to find the appropriate price (within a table created on a separate tab) based on these criterias. I'm a bit new to the usage of formulas and can't find the appropriate formula to use -- Dave Peterson |
select value in table based on values entered in multiple cell
Hi Dave,
It took a while to get your suggestion to work, but it works fine. First of all, I modified the table to suit the proposed match formula and I change all the (,) in the formula and replaced by (;). Thanks a lot, I had spent quite a few hours trying to figure this out. Cheers Claude "Dave Peterson" wrote: Maybe... Saved from a previous post: If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) claudard64 wrote: I'm trying to find a formula that will verify the value in many cells within a row and select a value in a table based on the values entered for those cells within the row. in one column, I indicate if the piece of wood in 4/4 or 5/4 in another column, I indicate the type of wood and finally in another column, I indicate the quality of wood. Based on these 3 criterias, I would like to find the appropriate price (within a table created on a separate tab) based on these criterias. I'm a bit new to the usage of formulas and can't find the appropriate formula to use -- Dave Peterson |
All times are GMT +1. The time now is 01:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com