#### jlajla24

##### New Member
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.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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))

Try

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

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)))

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?

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.

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

Replies
5
Views
234
Replies
1
Views
251
Replies
0
Views
239
Replies
3
Views
333
Replies
3
Views
1K

1,196,385
Messages
6,014,974
Members
441,860
Latest member
Store154

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back