Vlookup to SHow corresponding info

[CreativeRova]

Hi,

I have a table of info in in Column a has a bunch numbers all different. Column B shows names. i then have a separate spreadsheet with numbers similar to first spread sheet column A.

I want to check that the number that is in spreadsheet 2 if it appears in spreadsheet 1 then to show the name that is next to the number. see below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]1254
[/TD]
[TD][/TD]
[TD]John Smith
[/TD]
[TD][/TD]
[TD]269
[/TD]
[TD]Steph Johnson
[/TD]
[/TR]
[TR]
[TD]3562
[/TD]
[TD][/TD]
[TD]Bob Wright
[/TD]
[TD][/TD]
[TD]4259
[/TD]
[TD]Belinda Carter
[/TD]
[/TR]
[TR]
[TD]4541
[/TD]
[TD][/TD]
[TD]Simon Jones
[/TD]
[TD][/TD]
[TD]43637
[/TD]
[TD]Simone Hobbs
[/TD]
[/TR]
[TR]
[TD]3254
[/TD]
[TD][/TD]
[TD]Felix Unger
[/TD]
[TD][/TD]
[TD]3562
[/TD]
[TD]Joe Blog
[/TD]
[/TR]
[TR]
[TD]3526
[/TD]
[TD][/TD]
[TD]Joe Blog
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]269
[/TD]
[TD][/TD]
[TD]Steph Johnson
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4259
[/TD]
[TD][/TD]
[TD]Belinda Carter
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]43637
[/TD]
[TD][/TD]
[TD]Simone Hobbs
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12498
[/TD]
[TD][/TD]
[TD]Sarah Fogg
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As you can see column C has a number in itso i think i need to vlookup that number to column A, if its there then show column B next to that number.

 

[BrianM]

In Sheet2 (assuming the numbers are in column A) =VLOOKUP(A2,Sheet1!A:B,2,FALSE)

 

[FDibbins]

Your explanaition and cell refs are a bit confusing, but perhaps this will get you heading in the right direction?

As you can see column C has a number in

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

3​

[/td][td]1254[/td][td]not found[/td][td]John Smith[/td][td][/td][td]269[/td][td]Steph Johnson[/td][/tr]

[tr][td]

4​

[/td][td]3562[/td][td]Joe Blog[/td][td]Bob Wright[/td][td][/td][td]4259[/td][td]Belinda Carter[/td][/tr]

[tr][td]

5​

[/td][td]4541[/td][td]not found[/td][td]Simon Jones[/td][td][/td][td]43637[/td][td]Simone Hobbs[/td][/tr]

[tr][td]

6​

[/td][td]3254[/td][td]not found[/td][td]Felix Unger[/td][td][/td][td]3562[/td][td]Joe Blog[/td][/tr]

[tr][td]

7​

[/td][td]3526[/td][td]not found[/td][td]Joe Blog[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]269[/td][td]Steph Johnson[/td][td]Steph Johnson[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]4259[/td][td]Belinda Carter[/td][td]Belinda Carter[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]43637[/td][td]Simone Hobbs[/td][td]Simone Hobbs[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]12498[/td][td]not found[/td][td]Sarah Fogg[/td][td][/td][td][/td][td][/td][/tr]
[/table]

B3=IFERROR(VLOOKUP(A3,$E$3:$F$6,2,0),"not found")
copied down

 

[L. Howard]

Hi there,

Try...

On sheet 2 =VLOOKUP(E1,Sheet2!$A$1:$B$9,2,0)

Assumes the lookup numbers are in column E, then pull down.

Howard

 

[CreativeRova]

Hi Guys,

Sorry i think i should of been clearer. I tried the formulas stated above to no success.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet 1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3521
[/TD]
[TD]Joe Blog
[/TD]
[/TR]
[TR]
[TD]2698
[/TD]
[TD]Susan Jones
[/TD]
[/TR]
[TR]
[TD]451
[/TD]
[TD]Simon Hobb
[/TD]
[/TR]
[TR]
[TD]7548
[/TD]
[TD]Amy Blur
[/TD]
[/TR]
[TR]
[TD]6552
[/TD]
[TD]John Smith
[/TD]
[/TR]
[TR]
[TD]12415
[/TD]
[TD]Jane Topper
[/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD]Jarrod Long
[/TD]
[/TR]
[TR]
[TD]4115
[/TD]
[TD]Tina Small
[/TD]
[/TR]
[TR]
[TD]695
[/TD]
[TD]Matt Beard
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6599
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2698
[/TD]
[TD]Susan Jones
[/TD]
[/TR]
[TR]
[TD]1442
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]451
[/TD]
[TD]Simon Hobb
[/TD]
[/TR]
[TR]
[TD]12415
[/TD]
[TD]Jane Topper
[/TD]
[/TR]
</tbody>[/TABLE]


So on sheet 1 it shows number and name. On sheet 2 i want to check if the number that on sheet 2 appears on sheet 1 then show the corresponding name. (shown in green above)

 

[L. Howard]

Hi Guys,



So on sheet 1 it shows number and name. On sheet 2 i want to check if the number that on sheet 2 appears on sheet 1 then show the corresponding name. (shown in green above)

On sheet 1 column A is ALL the numbers.
On sheet 1 column B is ALL the names.

On sheet 2 the FEW numbers are in column A

On sheet 2 in column B =VLOOKUP(A1,Sheet1!$A$1:$B$9,2,0)

Then pull the formula down column B.

Howard

 

[Narendrareddy]

Try this

On sheet 2 in column B =IFERROR(VLOOKUP(A2,Sheet1!$A$1:$B$9,2,FALSE),"")

Then pull the formula down column B.

 

[FDibbins]

As indicated above, all you need to do is adjust the references to point to the other sheet

 

[CreativeRova]

thanks guys,

that worked perfectly.

 

[L. Howard]

To your off forum query:

Sheet Sponser:

First Last Donor-TFACTID
FN-1 LN-1
FN-2 LN-2
FN-3 LN-3
FN-4 LN-4
FN-5 LN-5

Where FN-1 is a first name & LN-1 is a last name in column A & B
Donor TFACTID is column C and the column you are seeking to fill in from Master sheet lookup (I may have the column name wrong, but it is C column)

In Donor-TFACTID column and pull down:
=IFERROR(VLOOKUP(A2&B2,Master!$A$2:$D$6,4,0),"")


Sheet Master:

Comb First Last Donor-TFACTID
FN-1LN-1 FN-1 LN-1 DT-1
FN-2LN-2 FN-2 LN-2 DT-2
FN-3LN-3 FN-3 LN-3 DT-3
FN-4LN-4 FN-4 LN-4 DT-4
FN-5LN-5 FN-5 LN-5 DT-5

Where In Comb column (column A as a "Helper Column") is the formula:

=B2&C2 and pull down

Me thinks I got it straight...

Give it a try with the ranges extended to suit your sheet.
Notice the $ $'s in the vlookup formula to secure the lookup range as you pull the formula down.

Howard