search and update

nnadimi19

Board Regular
Joined
Jul 14, 2002
Messages
240
I have a spread with sheet#1 with a whole bunch of columns, including in these columns are column "A" with check number and Column "B" with claim number. I have in sheet#2 several columns and including in those columns are column "A" with claim number and column "B" with policy. Is there a way to read the claim # from sheet#2 and find the corresponding claim # in sheet#1 and then add the policy number from sheet#2 to a column in sheet#1.

Thank you in advance
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
You have to use the VLOOKUP formula. It's quite easy using the button in the toolbar

Pali
 

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
no, no.... there's a button that looks like an "f"

it means to "paste a Function" with the function you need called "VLOOKUP"

have a try and if you get stuck (it's not the best help file) shout again and I'll send you my VLOOKUP example which is hopefully written in plain english rather then helpfilespeak
 

nnadimi19

Board Regular
Joined
Jul 14, 2002
Messages
240

ADVERTISEMENT

You are right the help does not work for me.
Please look at the example below and let me know if vlookup will work for that, if not any suggestions.

Thanks

Sheet#1

Column A Column B
Claim# Check#
1001 000001
2200 522211
3000 524242

Sheet#2
Column A Column B
Claim# Policy#
1001 56556
2200 587847
3000 56556

Final Result on Sheet #1 (after executing the lookup function)

Column A Column B Column C
Claim # Check # Policy #
1001 000001 56556
2200 522211 587847
3000 524242 56556
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-19 02:58, nnadimi19 wrote:
You are right the help does not work for me.
Please look at the example below and let me know if vlookup will work for that, if not any suggestions.

Thanks

Sheet#1

Column A Column B
Claim# Check#
1001 000001
2200 522211
3000 524242

Sheet#2
Column A Column B
Claim# Policy#
1001 56556
2200 587847
3000 56556

Final Result on Sheet #1 (after executing the lookup function)

Column A Column B Column C
Claim # Check # Policy #
1001 000001 56556
2200 522211 587847
3000 524242 56556

( 1.) Select all the relevant cells in Sheet2 excluding the labels.
( 2.) Go to the Name Box on the Formula Bar.
( 3.) Type PolicyTable and hit enter.

In Sheet1 in C2 enter and copy down:

=VLOOKUP(A2,PolicyTable,2,0)
 

nnadimi19

Board Regular
Joined
Jul 14, 2002
Messages
240

ADVERTISEMENT

I found a name option in the insert tab is that what you mean or is this name box somewhere else.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-19 03:39, nnadimi19 wrote:
I found a name option in the insert tab is that what you mean or is this name box somewhere else.

Look in the Help File for "Name Box".
 

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
There's a little window on the left of the toolbar in wich you can see the cell you are in. To name a range, select the whole range, and then write the name you want in that window.
Also, in order to use the VLOOKUP function, you can use a reference to a range as well as a name


Eg, if your policy table goes from A1 to B4:

=VLOOKUP(A3;Sheet2!$A$2:$B$4;2;0)

Enter this in C2 of Sheet1 and copy down

Pali
 

Forum statistics

Threads
1,144,122
Messages
5,722,604
Members
422,447
Latest member
knopp

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
Top