Referencing within a spreadsheet

[jlajla24]

I'm working on a price sheet for pictures and options.
My goal is to have an easy drop down type form that will take into consideration 2 separate values. Within a managed set of data, one option would be in a horizontal list, with the other in a vertical list, thereby referencing a single cell where the 2 intersect to autofill with the price.

To give a simple example:

Here is the form

1. SIZE X OPTION 1 $10.00


Here is what the data set would look like:


OPTION 1 OPTION 2 OPTION 3
SIZE X 10 11 12
SIZE Y 11 12 13
SIZE Z 12 13 14


Let me know if I can provide anything else that might help. I have a little experience with vertical referencing but not much. Thanks.

 

[Yard]

Sounds like you need an INDEX function with MATCH references.

If your data table is in A1:C4, your Size selection is in D1 and your Option selection is in E1, try this in F1:

=INDEX(A1:C4,MATCH(D1,A1:A4,0),MATCH(E1,A1:C1,0))

 

[xld]

Try

=INDEX(Sheet2!$A$1:$D$4,MATCH($A2,Sheet2!$A$1:$A$4,0),MATCH($B2,Sheet2!$A$1:$D$1,0))

 

[jlajla24]

Thank you gents!!!!

This is what I came up with after creating the data source on another worksheet
=IF(ISNA(INDEX(Sheet1!B2:H6, MATCH(B3, SIZE, 0),MATCH(C3,OPTIONS,0))),"",INDEX(Sheet1!B2:H6, MATCH(B3, SIZE, 0),MATCH(C3,OPTIONS,0)))

 

[jlajla24]

Question:

I am using this to fulfill another formula to multiply by quantity. I added the IF ISNA, when the 2 data cells are blank, the #NA wouldn't be filled. But, now that I want to multiply by a quantity cell, I get the #VALUE!, which I'm guessing would be fixed by the IF ISERROR. But, I don't know how to incorporate that.

Any ideas?

 

[Yard]

You mean you want to use the result of your INDEX function as a multiplier elsewhere, and you are getting a #VALUE! in that other cell?

Just change the "" in your formula to 0.

 

[jlajla24]

Got it and thank you!!!!!!!!!!!