lookup simultaneous 3 columns

S

saurabh_sharma

New Member
Joined
Apr 25, 2002
Messages
10
how can we look up values by lookup function if the data apoints are alongside in 3 diff columns- one after the other. Can we look up a no. of ids against a series of dates in the adjacent columns and their performance metrics in teh following columns.. an advanced for of step by step column filtering ?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
It is certainly possible. Depending on your data, either SUMPRODUCT, INDEX/MATCH, The Deatabase functions, or VBA seem to be likely candidates.

Please provide more details to get a specific response.

Bye,
Jay
 
Upvote 0
Thanks for the help.

I am facing an issue with the file below.
I need to draw a graph for GCVB ids for the diff. Dates for the diff parameters. I have tried using the pivot table but as the data is to be increased everyday so possibly a v lookup would help.
Pls. advise and help wit this…
calls contacts
4/25/02 GCVBMVT 10554 9497
4/25/02 GCVBSFT 7471 6483
4/25/02 GCVBMVT 10554 9497
4/24/02 GCVBIUT 607 537
4/24/02 GCVBIPT 11993 10859
4/24/02 GCVBKGT 5873 5085
4/24/02 GCVBIZT 10865 9958
4/23/02 GCVBIUT 607 537
4/23/02 GCVBIPT 11993 10859
4/23/02 GCVBKGT 5873 5085
4/23/02 GCVBIZT 10865 9958
4/23/02 GCVGSBT 4749 4255
4/23/02 GCVGVFT 9350 8428
4/23/02 GCVBIKT 8135 7319
4/23/02 GCVBPET 7954 7157
4/23/02 GCVBMZT 10426 9353
4/23/02 GCVBMWT 5935 5270
4/23/02 GCVBPAT 9740 8723
4/23/02 GCVBMVT 10554 9497
 
Upvote 0
On 2002-04-27 06:22, saurabh_sharma wrote:
Thanks for the help.

I am facing an issue with the file below.
I need to draw a graph for GCVB ids for the diff. Dates for the diff parameters. I have tried using the pivot table but as the data is to be increased everyday so possibly a v lookup would help.
Pls. advise and help wit this…
calls contacts
4/25/02 GCVBMVT 10554 9497
4/25/02 GCVBSFT 7471 6483
4/25/02 GCVBMVT 10554 9497
4/24/02 GCVBIUT 607 537
4/24/02 GCVBIPT 11993 10859
4/24/02 GCVBKGT 5873 5085
4/24/02 GCVBIZT 10865 9958
4/23/02 GCVBIUT 607 537
4/23/02 GCVBIPT 11993 10859
4/23/02 GCVBKGT 5873 5085
4/23/02 GCVBIZT 10865 9958
4/23/02 GCVGSBT 4749 4255
4/23/02 GCVGVFT 9350 8428
4/23/02 GCVBIKT 8135 7319
4/23/02 GCVBPET 7954 7157
4/23/02 GCVBMZT 10426 9353
4/23/02 GCVBMWT 5935 5270
4/23/02 GCVBPAT 9740 8723
4/23/02 GCVBMVT 10554 9497
Click to expand...

If you're going to create a pivot table from your data, you better use a dynamic name range and give this name as Range to the Wizard.

I'll assume the sample above to be in A1:D20 including labels/column headings in a worksheet called Data.

Activate Insert|Name|Define.
Enter Nrecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,Data!$A:$A)

Note that this formula uses your dates column.

Activate Add. (Don't leave yet the Define Name window.)

Enter Drange as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$A$1,0,0,Nrecs,4)

Note that 4 indicates how many columns your data area covers.

Activate OK.

Now give Drange as value of Range in PivotTable Wizard.

The above allows you to add to or delete from your data area as many records as you wish. At refresh PivotTable will know the new range.

Aladin
 
Upvote 0
Thanks for teh help wit pivot table
can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?

On 2002-04-27 06:52, Aladin Akyurek wrote:
On 2002-04-27 06:22, saurabh_sharma wrote:
Thanks for the help.

I am facing an issue with the file below.
I need to draw a graph for GCVB ids for the diff. Dates for the diff parameters. I have tried using the pivot table but as the data is to be increased everyday so possibly a v lookup would help.
Pls. advise and help wit this…
calls contacts
4/25/02 GCVBMVT 10554 9497
4/25/02 GCVBSFT 7471 6483
4/25/02 GCVBMVT 10554 9497
4/24/02 GCVBIUT 607 537
4/24/02 GCVBIPT 11993 10859
4/24/02 GCVBKGT 5873 5085
4/24/02 GCVBIZT 10865 9958
4/23/02 GCVBIUT 607 537
4/23/02 GCVBIPT 11993 10859
4/23/02 GCVBKGT 5873 5085
4/23/02 GCVBIZT 10865 9958
4/23/02 GCVGSBT 4749 4255
4/23/02 GCVGVFT 9350 8428
4/23/02 GCVBIKT 8135 7319
4/23/02 GCVBPET 7954 7157
4/23/02 GCVBMZT 10426 9353
4/23/02 GCVBMWT 5935 5270
4/23/02 GCVBPAT 9740 8723
4/23/02 GCVBMVT 10554 9497
Click to expand...

If you're going to create a pivot table from your data, you better use a dynamic name range and give this name as Range to the Wizard.

I'll assume the sample above to be in A1:D20 including labels/column headings in a worksheet called Data.

Activate Insert|Name|Define.
Enter Nrecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,Data!$A:$A)

Note that this formula uses your dates column.

Activate Add. (Don't leave yet the Define Name window.)

Enter Drange as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$A$1,0,0,Nrecs,4)

Note that 4 indicates how many columns your data area covers.

Activate OK.

Now give Drange as value of Range in PivotTable Wizard.

The above allows you to add to or delete from your data area as many records as you wish. At refresh PivotTable will know the new range.

Aladin
Click to expand...
 
Upvote 0
On 2002-04-27 07:37, saurabh_sharma wrote:
Thanks for teh help wit pivot table
can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?

You appear to want a formula-based approach. OK, but What is "GCVB ids for the diff. Dates for the diff parameters"? I mean it's not clear to me what the lookup values are? Care to elaborate on that?
 
Upvote 0
Hi Saurabh:
Thanks for teh help wit pivot table
can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?
Click to expand...

It is not quite clear what exactly you are asking, you do not however necessarily need a VLOOKUP function. Let us look into your data a little bit ... if the first record started in cell A2, then

A2 will house 4/25/2002
B2 will house GCVBMVT
C2 will house 10554
D2 will house 9497

so your entire dataset will be housed in cells A2:D20

Now let us say you are interested in locating a record with ID of GCVBIUT (say in cell F2)... you can locate in which row it lies by using the following formula in cell G2 ...

=MATCH($F2,B2:B20,0) will give 4 as the row number where this recod is encountered first

then
=INDEX($A$2:$D$20,MATCH($F2,$B$2:$B$20,0),1) will give you 4/24/2002 as the date associated with this record the first time this record is encountered

so you can also locate the other two pareameters associated with this record by adjusting the above formula

if you want to count the number of times this ID is encountered in your dataset, use the formula ...

=COUNTIF($B$2:$B$20,$F2) ... to give 2

I believe the formulas that you will need are covered in the set of formulas delineated above. I hope this will help you in your project of charting the data and the associated parameters.

Please post back if it works for you ... otherwise explain a little further and let us take it from there!
_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature
This message was edited by Yogi Anand on 2003-01-19 13:35
 
Upvote 0
Thanks for seeking more info.
I need to automate reports where performance by differnt associates appears in a consolidated report for 1 day. The macro is copying all the data one below the other. I need a graph of 1 particular id for all dates as a run chart and for 1 parameter at a time.
Hope it clarifies...
On 2002-04-27 08:19, Aladin Akyurek wrote:

On 2002-04-27 07:37, saurabh_sharma wrote:
Thanks for teh help wit pivot table
can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?

You appear to want a formula-based approach. OK, but What is "GCVB ids for the diff. Dates for the diff parameters"? I mean it's not clear to me what the lookup values are? Care to elaborate on that?
Click to expand...
 
Upvote 0
Hi Aladin:
I did not see your last post, before I posted mine -- as you stated, it is not clear what specifically Saurabh is asking.

Regards!

Yogi Anand
 
Upvote 0
Thanks for seeking more info.
I need to automate reports where performance by differnt associates appears in a consolidated report for 1 day. The macro is copying all the data one below the other. I need a graph of 1 particular id for all dates as a run chart and for 1 parameter at a time.
Hope it clarifies...



On 2002-04-27 08:33, Yogi Anand wrote:
Hi Aladin:
I did not see your last post, before I posted mine -- as you stated, it is not clear what specifically Saurabh is asking.

Regards!

Yogi Anand
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
253
Peter_SSs
Peter_SSs
R
  • Question
2 column lookup formula to reference new data
Replies
1
Views
159
awoohaw
awoohaw
S
  • Question
Lookup with multiple criteria in list with duplicate entries
Replies
1
Views
240
StephenCrump
S
D
  • Question
Update formula to check other conditions
Replies
1
Views
78
etaf
etaf
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
526
ahaye
A

Forum statistics

Threads
1,214,392
Messages
6,119,255
Members
448,879
Latest member
oksanana

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