Using lookups to find value based on two conditions.

[hgoodall]

Hello I am wanting to write a lookup which will bring back a value based on two conditions.

in sheet 1 I want to lookup Group (Column G) and Group counter(column H) and find the long text from sheet 2 column E.

(not all of the long text for each group is the same, each group counter can have different text, this is why I can't just do a lookup on the group)

Sheet 1

G H I

Group	Group Counter	Long text (want to bring back long text into here from sheet 2)
1110000	1
1110002	1
1110003	1
1110005	1
1110006	1
1110007	1
1110007	1
1110007	1
1110007	1

<tbody>
</tbody><colgroup><col><col><col></colgroup>

sheet 2

B C D E

Group	Group Counter	Short Text	Long Text
988	1	CHECK CONDITION OF SITE DOSING LINES	CHK SAMPLE, DOSING & MOTIVE WATER LINES.CHECK & INSPECT CONDITION OF LINES &CHAMBERS:CL2, pH & CONDUCTIVITY SAMPLE LINES,CL2INJECTION LINES,MOTIVE WATER LINES.
1032	1	Weekly site proactive task list	No long text
1154	1	TAKE ELECTRIC METER READING	No long text
1154	2	TAKE ELECTRIC METER READING	No long text
1154	3	TAKE ELECTRIC METER READING	No long text
1154	4	TAKE ELECTRIC METER READING	No long text
1154	5	TAKE ELECTRIC METER READING	No long text
1154	6	TAKE ELECTRIC METER READING	No long text

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[jmacleary]

If you are able to, I suggest you insert another column between H and I and put this formula in: '=CONCATENATE(G2,H2)', and on your sheet 2 a similar formula in a new column after column C. Then a lookup formula for the long text to lookup the concatenated data from column D in the sheet1 column I.

 

[Aladin Akyurek]

What is the relation between Group of Sheet1 and Group of Sheet2 as they don't seem to look similar?

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Group	Group Counter	Long text			Group	Group Counter		Short Text	Long Text
2​	1110000​	1​	A			1110000​	1​	CHECK	CONDITION	A
3​	1110000​	2​	B			1110000​	2​	Weekly	site	B
4​	1110003​	1​	C			1154​	1​	TAKE	ELECTRIC
5​	1110005​	1​	D			1154​	2​	TAKE	ELECTRIC
6​	1110006​	1​	E			1154​	3​	TAKE	ELECTRIC
7​	1110007​	1​	F			1154​	4​	TAKE	ELECTRIC
8​	1110007​	1​	G			1154​	5​	TAKE	ELECTRIC
9​	1110007​	1​	H			1154​	6​	TAKE	ELECTRIC
10​	1110007​	1​	I

Sheet: Sheet22

Array formula in cell J2:
=INDEX($C$2:$C$10,MATCH(F2&"|"&G2,$A$2:$A$10&"|"&$B$2:$B$10,0))

How to enter an array formula
1. Copy formula
2. Double click on cell J2
3. Paste formula
4. Press and hold CTRL + SHIFT
5. Press Enter

 

[Oscar Cronquist]

Regular formula in cell J2:
=INDEX($C$2:$C$10,MATCH(F2&"|"&G2,INDEX($A$2:$A$10&"|"&$B$2:$B$10,),0))