sumif or if or something else????

[durrienz]

Hi

I have a source table and a spreadsheet with 2 columns with pick lists.

Table1:

Type Size1 Size2 (and so on)
type1 $10.00 $10.50
type2 $12.00 $13.50

Column1 from picklist (lists type)
Column2 from picklist (lists size)

Column3 should (if possible) show the corresponding value if 'type1 and
size1' as an example or any mix that is chosen...

Hope this makes sense?

Thanks for any help or guidance

 

[Pete_UK]

Set up some named ranges:

Sizes covers the range of sizes that you have (eg Sheet1!$B$1:$M$1)
Types covers the range of types (eg Sheet1!$A$2:$A$100)
Table covers the numerical data in your table (eg Sheet1!$B$2:$M
$100)

Then you can use this formula (presumably in another sheet):

=INDEX(Table,MATCH(A1,Types,0),MATCH(B1,Sizes,0))

Hope this helps.

Pete

 

[durrienz]

Fantastic!

Thanks Pete, works a charm

Set up some named ranges:

Sizes covers the range of sizes that you have (eg Sheet1!$B$1:$M$1)
Types covers the range of types (eg Sheet1!$A$2:$A$100)
Table covers the numerical data in your table (eg Sheet1!$B$2:$M
$100)

Then you can use this formula (presumably in another sheet):

=INDEX(Table,MATCH(A1,Types,0),MATCH(B1,Sizes,0))

Hope this helps.

Pete