Multiple look ups

[MrRosco]

Hi everyone,

this is quite a difficult one i think, so here goes.

i want to know if its possible to search for an item and relative price based on a customer selection.

i have a form that my sales guys use for price changes and i want to link it too our database, now when the select the customer account number and add a product code in itll show
the specific item as well as in another cell show the live price.

im not sure if this can be done with a simple formula or will have to go down the whole VBA route

i hope i have covered enough information. if you need anything else please let me know.

 

[Comfy]

It depends!

As you've mentioned formulas and VBA am I correct in assuming your database is another Excel workbook/worksheet? Or is it something else?

You could use an INDEX/MATCH formula or if you can be certain that all entries are unique you could use a sumproduct.

 

[MrRosco]

It depends!

As you've mentioned formulas and VBA am I correct in assuming your database is another Excel workbook/worksheet? Or is it something else?

You could use an INDEX/MATCH formula or if you can be certain that all entries are unique you could use a sumproduct.

The database is a connected workbook whicj is populated from our system here, it is in a table format and gets refreshed daily but all of the columns remain the same,
product codes / Descriptions remain the same the only variable is the prices as these are unique to the customer i'm needing it to search specifically by customer then item code,description and price.

 

[Comfy]

Something like this?

Excel 2010

	A	B	C	D
customer_id	product_id	product_description	price
A	Product A
B	Product B
C	Product C
A	Product A
B	Product B
C	Product C

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]456[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]456[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]456[/TD]

[TD="align: right"]6[/TD]

</tbody>

db Example​

Excel 2010
	A	B
2	Customer	123
3	Product	B
4
5	Description	Price
6	Product B	2
Sheet2

Cell Formulas
Range	Formula
A6	{=INDEX('db Example'!$C$2:$C$7,MATCH(1,('db Example'!$A$2:$A$7=$B$2)*('db Example'!$B$2:$B$7=$B$3),0))}
B6	{=INDEX('db Example'!$D$2:$D$7,MATCH(1,('db Example'!$A$2:$A$7=$B$2)*('db Example'!$B$2:$B$7=$B$3),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MrRosco]

Something like this?

Excel 2010

	A	B	C	D
customer_id	product_id	product_description	price
A	Product A
B	Product B
C	Product C
A	Product A
B	Product B
C	Product C

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]456[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]456[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]456[/TD]

[TD="align: right"]6[/TD]

</tbody>

db Example​

Excel 2010

	A	B
Customer
Product	B
Description	Price
Product B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]123[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A6[/TH]
[TD="align: left"]{=INDEX('db Example'!$C$2:$C$7,MATCH(1,('db Example'!$A$2:$A$7=$B$2)*('db Example'!$B$2:$B$7=$B$3),0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]{=INDEX('db Example'!$D$2:$D$7,MATCH(1,('db Example'!$A$2:$A$7=$B$2)*('db Example'!$B$2:$B$7=$B$3),0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks for the reply it looks like it could be the answer, im not sure if it makes a differance but here is how the data is laid out

Database
Acc No. Col A
Item No. Col G
Item Name. Col H
Price Col N

Form
Account No. B4 (Currently a manual input which is connected to a simple lookup which populates the adjacent cell with the acc name)
Product code is cell A12
Product description cell B12
Current price D12

Hope this makes sense