Can I put a "lookup" dropdown in a table?

[JenniferMurphy]

Is there a way to have the values in one column of one table be the result of a dropdown into another table?

Suppose I have these two tables. Is there a way that when I click in the Product column of Table1, I am shown a dropdown of the entries in the Name column of Table2 and if I select one, the corresponding Lookup Value (or a link to it) is placed in that cell in Table1?

Table Lookup.xlsx
	B	C	D	E	F	G	H	I	J
5	Date	Product	Price	A	B	C		Name	Lookup Value
6	03/23/23	Lookup?	$249.95	??	??	??		Product A	Value X
7								Product B	Value Y
8								Product C	Value Z
Sheet1

Hmmm... I just noticed that xl2bb does not show the names of the tables. Table1 is the one on the left (<--). Table2 is the one on the right (-->).

 

[James006]

Hi,
If cell C6 can hold a Data Validation with your choices Value X, Value Y, Value Z
then formula for cell E6 :

=IF(SUBSTITUTE(INDEX($I:$I,MATCH($C6,$J:$J,0)),"Product ","")=E$5,INDEX($I:$I,MATCH($C6,$J:$J,0)),"")

 

[JenniferMurphy]

Hi,
If cell C6 can hold a Data Validation with your choices Value X, Value Y, Value Z
then formula for cell E6 :

=IF(SUBSTITUTE(INDEX($I:$I,MATCH($C6,$J:$J,0)),"Product ","")=E$5,INDEX($I:$I,MATCH($C6,$J:$J,0)),"")

Thanks for taking the time to work on my very poorly worded problem. I don't know what I was thinking. What I am trying to do is actually quite straightforward once I get my head on straight. The table will be processed by a macro. The macro will do the lookup. Here are better sample tables. Suppose I am in the market for a new EV. Table1 will contain various properties of cars that I am considering buying. Many of the properties, such as MSRP & Range are numeric. They can be assessed arithmetically. Others are non-numeric properties, like body type. For those, I need a lookup table to convert them to a numeric value. Then the macro can calculate an overall rating.

Table Lookup.xlsx
	B	C	D	E	F	G	H	I
4	Table1						Table2
5	Make	Model	Body Type	MSRP	Range		Body Type	Value
6	Hyundai	Ioniq 5	SUV	$41K	220		Sedan	10
7	BMW	iX	SUV	$109K	288		Hatchback	9
8	Chevrolet	Bolt EUV	Hatchback	$28K	259		Coupe	7
9	BMW	i4	Sedan	$56K	260		SUV	6
10	Genesis	G80	Sedan	$80K	282		Minivan	6
11							Sports Car	5
12							Station Wagon	2
13							Convertible	0
14							Pickup	0
Sheet1

I apologize for taking up your time on this wild goose chase. Thanks, again.