Find value in table and return value from other table.

[Otchoo]

Hi,

I am looking for a way to find a value in a table but get the value of another table.

Like the example: i'm searching for number 7. But i need the result: Letter G

I hope somebody can help me.

Greetz Johan

 

[Eric W]

Try:

Book1
	A	B	C
1
2	Lookup	7
3	Return	G
4
5
6	1	6	11
7	2	7	12
8	3	8	13
9	4	9	14
10	5	10	15
11
12	A	F	K
13	B	G	L
14	C	H	M
15	D	I	N
16	E	J	O
Sheet8

Cell Formulas
Range	Formula
B3	B3	=CONCAT(IF(B2=A6:C10,A12:C16,""))

 

[Coyotex3]

Try:

Book1
	A	B	C
1
2	Lookup	7
3	Return	G
4
5
6	1	6	11
7	2	7	12
8	3	8	13
9	4	9	14
10	5	10	15
11
12	A	F	K
13	B	G	L
14	C	H	M
15	D	I	N
16	E	J	O
Sheet8

Cell Formulas
Range	Formula
B3	B3	=CONCAT(IF(B2=A6:C10,A12:C16,""))

Not OP, but super interested in knowing how this formula is working!

 

[Otchoo]

Try:

Book1
	A	B	C
1
2	Lookup	7
3	Return	G
4
5
6	1	6	11
7	2	7	12
8	3	8	13
9	4	9	14
10	5	10	15
11
12	A	F	K
13	B	G	L
14	C	H	M
15	D	I	N
16	E	J	O
Sheet8

Cell Formulas
Range	Formula
B3	B3	=CONCAT(IF(B2=A6:C10,A12:C16,""))

Hi,

Thx for the reply and help. But it is not what i am looking for. It gives me the right answer, but it places it in some kind of window. So when i change the lookup value the answer jumps to another place in the windows.

It needs to stay in the same cell.

 

[Coyotex3]

Hi,

Thx for the reply and help. But it is not what i am looking for. It gives me the right answer, but it places it in some kind of window. So when i change the lookup value the answer jumps to another place in the windows.

It needs to stay in the same cell.

Oddly enough, I'm not getting that on my end.

Did your formula include the "Concat" portion as well? That should prevent the additional window from showing up.

 

[Otchoo]

Oddly enough, I'm not getting that on my end.

Did your formula include the "Concat" portion as well? That should prevent the additional window from showing up.

View attachment 104397

Thx that made it work. Made a mistake in the translation to te dutch code.

Thx for the help.

 

[Eric W]

Coyotex3, I think you figured it out by now. The IF compares B2 with each value in the first table, and if it matches, returns the corresponding value in the second table, otherwise a "". Then the CONCAT puts them all together, and only the desired value will be left. Thanks for the assist.

Otchoo, looks like you got it figured out. Glad we could help.

 

[Coyotex3]

@Otchoo please mark Eric W’s answer as the solution. He is the one which provided you with the solution! I just added a question and some input!!

 

[Otchoo]

Coyotex3, I think you figured it out by now. The IF compares B2 with each value in the first table, and if it matches, returns the corresponding value in the second table, otherwise a "". Then the CONCAT puts them all together, and only the desired value will be left. Thanks for the assist.

Otchoo, looks like you got it figured out. Glad we could help.

Thx for your help.