Lookup by text in Row1?

**soundengineer** · 04-18-2007, 09:16 PM

so I have a sheet that the first row is a descriptive row...
all the other rows are numerical values
I want to be able to look something up by a full column using the descriptor in the first line..
example being
"Throttle Position (SAE) %" is the label of Column E
and instead of saying E:E I want to say "Throttle Position (SAE) %"

reason being that this may be positioned differently and may not always fall in the E column...and the formulas I have on another sheet will need to lookup and find that column on its own

this sheet shows the first line with descriptors and the rest with data values..

**BigBas** · 04-18-2007, 11:14 PM

Out of curiosity, did the solution I gave you not work?

**soundengineer** · 04-18-2007, 11:37 PM

Originally Posted by BigBas

Out of curiosity, did the solution I gave you not work?

honestly I think I scrambled my brains with the several things you've helped me on so far...

the last thing you sent after the PM reply did not work....or most likely I didnt use it correctly...definately most likely operator error on my part most likely
all I can say is..
that spread sheet I e-mailed you...
I need to make it so that anybody who pastes in their worksheet data...will be able to use the spread sheet..the worksheet data will be different from every person who uses it...so I will need it to have a way to look up the info it needs for calculations based on the first row

did you get the e-mail with the other things that I cant figure out??

**soundengineer** · 04-18-2007, 11:53 PM

Actually..I just looked at the last file you repied with in the other thread....
it makes no sens at all to me....I dont know what its saying..I just dont understand the formula arguments that you used..and I definately didnt need it as a dropdown for that example..LOL
and it was a different topic than this one anyways

**BigBas** · 04-19-2007, 12:14 AM

Strangely enough, I did not receive your email, and I check both my regular and spam mailbox. I will work with this file here for now:

Lets say you want to find the MAX speed based on Throttle Position. You can use a formula like this (remember to confirm with CTRL+SHIFT+ENTER

=MAX(IF(OFFSET($A$1,1,MATCH("Throttle Position (SAE) %",$A$1:$H$1,0)-1,COUNTA($A:$A)-1,1)=100,OFFSET($A$1,1,MATCH("Vehicle Speed (SAE) mph",$A$1:$H$1,0)-1,COUNTA($A:$A)-1,1)))

In this formula, you can get a better idea of how it is working. You are matching throttle position at 100, and finding the max vehicle speed. The problem with this method is that it is not very dynamic; if you want to now find the MAX vehicle speed based on Ignition Timing (for example), you have to go within the formula, and change the variables. However, when you have the formula refer to cells, you can always change the variables within the cell, saving you some time.

Check the example below. On Sheet 2, I have changed the order around a bit, and it still works. This will work, as long as the information has the lable in row 1.

**soundengineer** · 04-19-2007, 07:07 PM

so I think "MATCH" is the term I really need after reading a little in the excel help file..
how do I use match with a different page lookup?
meaning I'm using Data Logged'!E:E so I need to make sure I can look up the"Match" on that page

maybe I'm possibly looking at this wrong...
maybe its HLookup??

just as long as I can lookup a whole column by the 1st line of the column

**soundengineer** · 04-19-2007, 07:46 PM

Originally Posted by soundengineer

so I think "MATCH" is the term I really need after reading a little in the excel help file..
how do I use match with a different page lookup?
meaning I'm using Data Logged'!E:E so I need to make sure I can look up the"Match" on that page

maybe I'm possibly looking at this wrong...
maybe its HLookup??

just as long as I can lookup a whole column by the 1st line of the column

and heres the file I am talking about...
data logging page will change as its a user input page
the columns will not always be in the same positions so I need to be able to lookup columns by the name in the first row of the columns

the "converter efficiency" is a good example to use
right now the formula in that cell is
=(AVERAGEIF('Data Logged'!E:E,"=100",'Data Logged'!S:S))/(AVERAGEIF('Data Logged'!E:E,"=100",'Data Logged'!C:C))

right now E represents "Throttle Position (SAE) %"
but Throttle Position (SAE) % could end up in any column..so I want to make it so that no matter where it falls...the formula will look for "Throttle Position (SAE) %" so if it should end up in G or F or AA or wherever...it will find it and the formula will still work correctly

so somebody replace the 'Data Logged'!E:E portion of the formula above so that it can just look it up by "Throttle Position (SAE) %"
make it work properly and tell me what the heck I need to do since I cant figure it out myself for some reason

http://www.audiohelix.com/excel%20fo...Calibrator.xls

Lookup by text in Row1?

LinkBack

Thread Tools

Rate This Thread

Display

Lookup by text in Row1?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions