vhlookup or cell to cell corelation

H

happychronicles

New Member
Joined
Nov 22, 2017
Messages
5
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
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.
 
Upvote 0
[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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
459
Rnkhch
R
M
  • Question Question
VLOOKUP?
Replies
1
Views
179
MeaclH
M
E
  • Solved
Filter and Textjoin formula if two columns match a text value in a cell - excel 365 in a Mac
Replies
7
Views
319
Fluff
Fluff
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
259
John_w
J
G
  • Question Question
Filling cells with multiple " IF " & " THEN " in the formula
Replies
0
Views
259
Guy Boot
G

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top