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[TABLE="width: 521"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Group Counter[/TD]
[TD]Long text (want to bring back long text into here from sheet 2)[/TD]
[/TR]
[TR]
[TD]1110000 [/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110002[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110003[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110005[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110006[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110007[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110007[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110007[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1110007[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


sheet 2

B C D E[TABLE="width: 1627"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Group Counter[/TD]
[TD]Short Text[/TD]
[TD]Long Text[/TD]
[/TR]
[TR]
[TD]988[/TD]
[TD]1[/TD]
[TD]CHECK CONDITION OF SITE DOSING LINES[/TD]
[TD]CHK SAMPLE, DOSING & MOTIVE WATER LINES.CHECK & INSPECT CONDITION OF LINES &CHAMBERS:CL2, pH & CONDUCTIVITY SAMPLE LINES,CL2INJECTION LINES,MOTIVE WATER LINES.[/TD]
[/TR]
[TR]
[TD]1032[/TD]
[TD]1[/TD]
[TD]Weekly site proactive task list[/TD]
[TD]No long text[/TD]
[/TR]
[TR]
[TD]1154[/TD]
[TD]1[/TD]
[TD]TAKE ELECTRIC METER READING[/TD]
[TD]No long text[/TD]
[/TR]
[TR]
[TD]1154[/TD]
[TD]2[/TD]
[TD]TAKE ELECTRIC METER READING[/TD]
[TD]No long text[/TD]
[/TR]
[TR]
[TD]1154[/TD]
[TD]3[/TD]
[TD]TAKE ELECTRIC METER READING[/TD]
[TD]No long text[/TD]
[/TR]
[TR]
[TD]1154[/TD]
[TD]4[/TD]
[TD]TAKE ELECTRIC METER READING[/TD]
[TD]No long text[/TD]
[/TR]
[TR]
[TD]1154[/TD]
[TD]5[/TD]
[TD]TAKE ELECTRIC METER READING[/TD]
[TD]No long text[/TD]
[/TR]
[TR]
[TD]1154[/TD]
[TD]6[/TD]
[TD]TAKE ELECTRIC METER READING[/TD]
[TD]No long text[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[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]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Group[/td][td]Group Counter[/td][td]Long text[/td][td][/td][td][/td][td]Group[/td][td]Group Counter[/td][td][/td][td]Short Text[/td][td]Long Text[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1110000​

[/td][td]

1​

[/td][td]A[/td][td][/td][td][/td][td]

1110000​

[/td][td]

1​

[/td][td]CHECK[/td][td]CONDITION[/td][td]A[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1110000​

[/td][td]

2​

[/td][td]B[/td][td][/td][td][/td][td]

1110000​

[/td][td]

2​

[/td][td]Weekly[/td][td]site[/td][td]B[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1110003​

[/td][td]

1​

[/td][td]C[/td][td][/td][td][/td][td]

1154​

[/td][td]

1​

[/td][td]TAKE[/td][td]ELECTRIC[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

1110005​

[/td][td]

1​

[/td][td]D[/td][td][/td][td][/td][td]

1154​

[/td][td]

2​

[/td][td]TAKE[/td][td]ELECTRIC[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

1110006​

[/td][td]

1​

[/td][td]E[/td][td][/td][td][/td][td]

1154​

[/td][td]

3​

[/td][td]TAKE[/td][td]ELECTRIC[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

1110007​

[/td][td]

1​

[/td][td]F[/td][td][/td][td][/td][td]

1154​

[/td][td]

4​

[/td][td]TAKE[/td][td]ELECTRIC[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

1110007​

[/td][td]

1​

[/td][td]G[/td][td][/td][td][/td][td]

1154​

[/td][td]

5​

[/td][td]TAKE[/td][td]ELECTRIC[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

1110007​

[/td][td]

1​

[/td][td]H[/td][td][/td][td][/td][td]

1154​

[/td][td]

6​

[/td][td]TAKE[/td][td]ELECTRIC[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

1110007​

[/td][td]

1​

[/td][td]I[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet22[/td][/tr][/table]

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))