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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You have to use the VLOOKUP formula. It's quite easy using the button in the toolbar

Pali
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
I found a name option in the insert tab is that what you mean or is this name box somewhere else.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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