Excel 2010 - Lookup multiple values on other sheets in response to clicking a cell -

i trying take specific information 1 sheet, , have show in according specific cell showing.

what have far: when click cell in work sheet, shows information given row in d3-i3.

what want: take information f3 , i3 , reference prices in sheet (price sheeet), , show them in e4-e7. way can update price page, , show price on inventory sheets. want them change click around, same code already.

code im using far.

private sub worksheet_selectionchange(byval target range)     if target.row > 0 , target.column > 0         range("d2").value = cells(target.row, 4).value         range("e2").value = cells(target.row, 5).value         range("f2").value = cells(target.row, 3).value         range("g2").value = cells(target.row, 7).value         range("h2").value = cells(target.row, 8).value         range("i2").value = cells(target.row, 10).value     end if end sub 

i rearranged want prices show, , how in image linked below. (mock inventory sheet) http://i.stack.imgur.com/yaksh.png

and here's mock of price sheet. http://i.stack.imgur.com/heq95.png

please read comment replys.

i think best use regular functions in case

the strategy use in case first search column gb resides in.

=match(f3,prices!1:1,0) 

for 20gb, return "7" meaning in 7th column left (column g)

i search interface row. see have choice between 2.5 , 3.5 options simple if statement need made (but isn't included in example sheet im assuming 3.5)

=match(i3,prices!a:a,0) 

in example, return 3 (row 3). since have x , y pretty easy reference cell , there number of different ways it.

one way offset. in 'price' cell want this:

=offset(prices!a1,match(i3,prices!a:a,0)-1,match(f3,prices!1:1,0)-1) 

what @ cell a1 in prices worksheet (your reference), , tells move 3-1 rows down (rows operator), , tells move 7-1 rows right (columns operator)

you adjust row offset fit other fields cost accept, , decline. cost -1, accept = +1, decline = +2

cost =

=offset(prices!a1,match(i3,prices!a:a,0)-2,match(f3,prices!1:1,0)-1) 

you set logic 3.5 , 2.5 situations , logical test if product = 2.5, use 2.5 model. else use 3.5 model. may need tweak existing book that

additionally, may need tweak fit book since had recreate data , may have of columns misaligned