Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup or reference 3 data points
I have a data set from A3 to D60000.
In any selected row the data values in columns A, B and C correspond to a value in column D. Eg. If A4=1.04, B4=.96 and C4=.75 then the value in D4= 25 I have a similar data set F3 to I60000. Similarly in any selected row the data values in columns F, G and H correspond to a value in column I. I need a lookup or reference formula such that I can find the value of column I for the data group in columns A, B and C. I.e. if I reference A9847, B98847 and C9847 the formula would find the value in column I that has those same data values. I know similar questions have been posted before and I tried to adapt those answers to my problem but couldnt get a working formula. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup or reference 3 data points
Why not show us a small sample of say 10 rows.
It is unclear what the 25 in D4 has to do with the problem " I need a lookup or reference formula such that I can find the value of column I for the data group in columns A, B and C. I.e. if I reference A9847, B98847 and C9847 the formula would find the value in column I that has those same data values" Please explain more. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "AG" wrote in message ... I have a data set from A3 to D60000. In any selected row the data values in columns A, B and C correspond to a value in column D. Eg. If A4=1.04, B4=.96 and C4=.75 then the value in D4= 25 I have a similar data set F3 to I60000. Similarly in any selected row the data values in columns F, G and H correspond to a value in column I. I need a lookup or reference formula such that I can find the value of column I for the data group in columns A, B and C. I.e. if I reference A9847, B98847 and C9847 the formula would find the value in column I that has those same data values. I know similar questions have been posted before and I tried to adapt those answers to my problem but couldn't get a working formula. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup or reference 3 data points
On Tue, 22 Apr 2008 12:20:01 -0700, AG
wrote: I have a data set from A3 to D60000. In any selected row the data values in columns A, B and C correspond to a value in column D. Eg. If A4=1.04, B4=.96 and C4=.75 then the value in D4= 25 I have a similar data set F3 to I60000. Similarly in any selected row the data values in columns F, G and H correspond to a value in column I. I need a lookup or reference formula such that I can find the value of column I for the data group in columns A, B and C. I.e. if I reference A9847, B98847 and C9847 the formula would find the value in column I that has those same data values. I know similar questions have been posted before and I tried to adapt those answers to my problem but couldn’t get a working formula. I can't see what the information about D4 has to do with the problem. If you are looking for the value in the I column that is on the same row as the three data in columns F,G,H that are equal to the three data in columns A,B,C (but on another row) this is one way to go. I assume that column E that has not been mentioned is free to use as a helper column, In cell E3 enter =F3&G3&H3 copy down as far as you have data in columns F,G,H,I The result I put in column J (which is the first free column) In cell J3 enter =VLOOKUP(A3&B3&C3,E$3:I$1000,5,FALSE) change 1000 to the last row with data in columns F,G,H,I copy down as far as you have data in columns F,G,H,I If the three data A,B,C are not present anywhere in columns F,G,H the result in column J will be #N/A. Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup or reference 3 data points
Sorry for the confusing post. The value in column D is only indirectly
related to my request. I had previously sorted the range A3:D60000 descending by column D so that the values in D go form high to low. That being said your formulas did provide a means to the answer I was looking to find. Thank you. "Lars-Ã…ke Aspelin" wrote: On Tue, 22 Apr 2008 12:20:01 -0700, AG wrote: I have a data set from A3 to D60000. In any selected row the data values in columns A, B and C correspond to a value in column D. Eg. If A4=1.04, B4=.96 and C4=.75 then the value in D4= 25 I have a similar data set F3 to I60000. Similarly in any selected row the data values in columns F, G and H correspond to a value in column I. I need a lookup or reference formula such that I can find the value of column I for the data group in columns A, B and C. I.e. if I reference A9847, B98847 and C9847 the formula would find the value in column I that has those same data values. I know similar questions have been posted before and I tried to adapt those answers to my problem but couldnt get a working formula. I can't see what the information about D4 has to do with the problem. If you are looking for the value in the I column that is on the same row as the three data in columns F,G,H that are equal to the three data in columns A,B,C (but on another row) this is one way to go. I assume that column E that has not been mentioned is free to use as a helper column, In cell E3 enter =F3&G3&H3 copy down as far as you have data in columns F,G,H,I The result I put in column J (which is the first free column) In cell J3 enter =VLOOKUP(A3&B3&C3,E$3:I$1000,5,FALSE) change 1000 to the last row with data in columns F,G,H,I copy down as far as you have data in columns F,G,H,I If the three data A,B,C are not present anywhere in columns F,G,H the result in column J will be #N/A. Hope this helps / Lars-Ã…ke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup or reference 3 data points
Sorry for the confusing post. The value in column D is only indirectly
related to my request. I had previously sorted the range A3:D60000 descending by column D so that the values in D go form high to low. That being the formulas from Lars did provide a means to the answer I was looking to find. Thank you. "AG" wrote: I have a data set from A3 to D60000. In any selected row the data values in columns A, B and C correspond to a value in column D. Eg. If A4=1.04, B4=.96 and C4=.75 then the value in D4= 25 I have a similar data set F3 to I60000. Similarly in any selected row the data values in columns F, G and H correspond to a value in column I. I need a lookup or reference formula such that I can find the value of column I for the data group in columns A, B and C. I.e. if I reference A9847, B98847 and C9847 the formula would find the value in column I that has those same data values. I know similar questions have been posted before and I tried to adapt those answers to my problem but couldnt get a working formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting data series - line between certain data points only | Charts and Charting in Excel | |||
excel 2007, how to select a data point and cycle through data points | Charts and Charting in Excel | |||
Formula using 2 points of reference to return a value from a table | Excel Worksheet Functions | |||
Linear interpolation between two points found using a lookup funct | Excel Worksheet Functions | |||
Excel Reference points to original file | Excel Discussion (Misc queries) |