To get value in another sheet dynamically

jagadeeshds

New Member
Joined
Jan 25, 2018
Messages
5
Dear members,

I need to get amount and name stored in sheet2 by comparing id numbers stored in sheet1

Sheet2 values


A B C H

______________________________

1 1000000 DM

2 SS100148 DM2 350

3 SS100278 Dm3 350

4 SS100364 DM4 1050

5 SS100473 DM5 350

6 SS100584 DM6 1400

7 SS100616 DM7 1050

-----------------------------------------------------------------------

Getting values in Sheet1 (Contains columns A to F)


A B C D E F

S. No. ID -- Name -- Amount
---------------------------------------------------
1 SS100584 1400
2 SS100616 1050

Here, i got amount ( H column in sheet2 by compaing id coumn in sheet1 compoaing with b column in sheet2 ) using the formula

=SUM(SUMIF(Sheet2!B5:Sheet2!B140,B2,Sheet2!H5:Sheet2!H140))

But, i am not able to get name (C Column in sheet2 by compaing id coumn in sheet1 compoaing with b column in sheet2) . Immediate help is required.

Thanks in advance.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Each ID number in column B occurs only once. If this is not the case solution will not work.
Hence the need to supply accurate data.

Since the ID occurs only once this is a simple case of VLOOKUP or INDEX(...MATCH())

(I cant make out where your columns are in Sheet1)

in Sheet1!B1
=VLOOKUP(B1,Sheet1:B$1:H$7,2,0)
will return the name

in Sheet1!D1
=VLOOKUP(B1,Sheet1:B$1:H$7,7,0)
will return the amount
 
Last edited:
Upvote 0
Thanks for your reply.
Yes you are right. ID value occurs only once in both sheets.

ID columns in sheet1 (Sheet1!C2) has to be compared with ID Column in Sheet2
(Sheet2!B5). Here in this vlookup function i don't know where sheet2 data is compared. kindly explain. i am not getting the result.

Each ID number in column B occurs only once. If this is not the case solution will not work.
Hence the need to supply accurate data.

Since the ID occurs only once this is a simple case of VLOOKUP or INDEX(...MATCH())

(I cant make out where your columns are in Sheet1)

in Sheet1!B1
=VLOOKUP(B1,Sheet1:B$1:H$7,2,0)
will return the name

in Sheet1!D1
=VLOOKUP(B1,Sheet1:B$1:H$7,7,0)
will return the amount
 
Upvote 0
Oops, my mistake

The results should are going into Sheet 2 aren't they?

in which case that should be

in Sheet2!B1
=VLOOKUP(B1,Sheet1:B$1:H$7,2,0)
will return the name

in Sheet2!D1
=VLOOKUP(B1,Sheet1:B$1:H$7,7,0)
will return the amount
 
Upvote 0
Dear Sir,
Thanks for your immediate reply.

My concept is we will paste data in sheet2. Depending on the new data we have to calculate in sheet1 as given in very first question.

We have to compare all data in Sheet2!B5:Sheet2!B262 with Sheet1!B1:Shhet1!B12. And weekly Sheet 2 will be get updated, at that time we have to get result automatically. Hope, this is enough.

Oops, my mistake

The results should are going into Sheet 2 aren't they?

in which case that should be

in Sheet2!B1
=VLOOKUP(B1,Sheet1:B$1:H$7,2,0)
will return the name

in Sheet2!D1
=VLOOKUP(B1,Sheet1:B$1:H$7,7,0)
will return the amount
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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