vhlookup or cell to cell corelation

[happychronicles]

Hi,

I have a list of data that correlates with each other.
I need to be able to do an =A!:A150, if true then this cells = X
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl81, width: 64"]Bl[/TD]
[TD="class: xl81, width: 64"]T[/TD]
[TD="class: xl82, width: 64"] No.[/TD]
[/TR]
[TR]
[TD="class: xl83"]1[/TD]
[TD="class: xl83"]3[/TD]
[TD="class: xl83"]1[/TD]
[/TR]
[TR]
[TD="class: xl83"]1[/TD]
[TD="class: xl83"]1[/TD]
[TD="class: xl83"]2[/TD]
[/TR]
[TR]
[TD="class: xl83"]1[/TD]
[TD="class: xl83"]4[/TD]
[TD="class: xl83"]3[/TD]
[/TR]
[TR]
[TD="class: xl83"]1[/TD]
[TD="class: xl83"]2[/TD]
[TD="class: xl83"]4[/TD]
[/TR]
[TR]
[TD="class: xl83"]2[/TD]
[TD="class: xl83"]2[/TD]
[TD="class: xl83"]5[/TD]
[/TR]
[TR]
[TD="class: xl83"]2[/TD]
[TD="class: xl83"]3[/TD]
[TD="class: xl83"]6[/TD]
[/TR]
[TR]
[TD="class: xl83"]2[/TD]
[TD="class: xl83"]1[/TD]
[TD="class: xl83"]7[/TD]
[/TR]
[TR]
[TD="class: xl83"]2[/TD]
[TD="class: xl83"]4[/TD]
[TD="class: xl83"]8[/TD]
[/TR]
[TR]
[TD="class: xl83"][/TD]
[TD="class: xl83"][/TD]
[TD="class: xl83"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]BL[/TD]
[TD]t[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=3[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Any Help would be much appriciated.
Thank you, Jess

 

[Marcílio_Lobão]

Jess, Good evening.

"...I need to be able to do an =A!:A150..."
What is this???

Scenario:
[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]A
[/TD]
[TD="width: 64, align: center"]B
[/TD]
[TD="width: 64, align: center"]C
[/TD]
[TD="width: 64, align: center"]D
[/TD]
[TD="width: 64, align: center"]E
[/TD]
[TD="width: 64, align: center"]F
[/TD]
[TD="width: 64, align: center"]G
[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]1
[/TD]
[TD="width: 64, align: center"]BL
[/TD]
[TD="width: 64, align: center"]T
[/TD]
[TD="width: 64, align: center"] No.
[/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]BL[/TD]
[TD="width: 64, align: center"]T[/TD]
[TD="width: 64, align: center"]Nº[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]=formula
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Try to use:

=SUMPRODUCT((A2:A9=E2) * (B2:B9=F2) * (C2:C9))

Is that what you want?
I hope it helps.

 

[happychronicles]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BL[/TD]
[TD]T[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD]BL[/TD]
[TD]T[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD](Should be 3)[/TD]
[TD](should be 4)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]should be 3[/TD]
[TD]should be 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Should be 4[/TD]
[TD]Should be 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]should be 4[/TD]
[TD]should be 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]should be 4[/TD]
[TD]should be 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi I hope that this helps clear up what I am looking for.
Thank you for your time and sorry for not making myself clear.

 

[happychronicles]

Hi, That isn't what I am looking for. I will try to make my question more coherent.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]BL[/TD]
[TD]T[/TD]
[TD]NO.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]TABLE 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]BL[/TD]
[TD]T[/TD]
[TD]NO.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SHOULD =2[/TD]
[TD]SHOULD =4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SHOULD =2[/TD]
[TD]SHOULD = 4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SHOULD = 2[/TD]
[TD]SHOULD = 4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SHOULD =3 [/TD]
[TD]SHOULD = 6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SHOULD =3[/TD]
[TD]SHOULD -6 [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SHOULD = 3[/TD]
[TD]SHOULD =6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]SHOULD =4 [/TD]
[TD]SHOULD = 7[/TD]
[/TR]
</tbody>[/TABLE]

I would like to have a formula for table two for columns B and C that are drived from table one based on column A in table 1.
Thank you for your time.

 

[Marcílio_Lobão]

Jess,

Now your original table is very different from your first message.

Then the logic will be different and much simple.

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]A
[/TD]
[TD="width: 64, align: center"]B
[/TD]
[TD="width: 64, align: center"]C
[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]1
[/TD]
[TD="width: 64, align: center"]BL
[/TD]
[TD="width: 64, align: center"]T
[/TD]
[TD="width: 64, align: center"] No.
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]A
[/TD]
[TD="width: 64, align: center"]B
[/TD]
[TD="width: 64, align: center"]C
[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]40
[/TD]
[TD="width: 64, align: center"]BL
[/TD]
[TD="width: 64, align: center"]T
[/TD]
[TD="width: 64, align: center"] No.
[/TD]
[/TR]
[TR]
[TD="align: center"]41
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]=formula[/TD]
[TD="align: center"]=formula[/TD]
[/TR]
[TR]
[TD="align: center"]42
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]43
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]44
[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Try this one:

B41 --> =VLOOKUP(A41, $A$2:$C$5, 2, false)

C41 --> =VLOOKUP(A41, $A$2:$C$5, 3, false)

Copy the formulas down.

Adapt cell reference to your reality.

Is that what you want?
I hope it helps.

 

[happychronicles]

I will try this!
Thank you for your response!