Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 14th 08, 12:01 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 2
Default 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

  #2   Report Post  
Old November 14th 08, 12:36 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 411
Default 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)

  #3   Report Post  
Old November 14th 08, 01:57 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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
  #4   Report Post  
Old November 16th 08, 01:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 2
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lost ability to select multiple cells w/ values & see sum, why? Marianne New Users to Excel 1 October 19th 07 01:00 AM
Select correct worksheet based on data entered into a cell Harry Stevens Excel Worksheet Functions 2 May 13th 07 07:57 AM
how do I make drop-down list select based on 1st letter entered? Missykender Excel Discussion (Misc queries) 3 October 6th 05 11:55 PM
can excel draw a shape based on values entered in cells Milo Excel Worksheet Functions 1 April 6th 05 01:36 AM
Selecting data from a list based on entered values GrantM Excel Discussion (Misc queries) 1 December 20th 04 11:59 AM


All times are GMT +1. The time now is 06:42 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017