The example that I have attached explains everything. In short:
I have a data table and I am trying to return a value that will look for the ROW matching 2 different criteria, then locate the appropriate COLUMN and return the value from said ROW.
Last edited by sinspawn56; 04-09-2010 at 06:05 PM.
In B14, try:
=INDEX($F$2:$H$7,MATCH(1,INDEX(($D$2:$D$7=B$13)*($B$2:$B$7=$A$13),0),0),MATCH($A14,$F$1:$H$1,0))
copied across and down.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
That is mostly working, but I am getting a #VALUE error in some of my cells. In my actual F1:H1 there are whole phrases. In those phrases there are double hyphens (--) that are separating the sentence structure. I believe this is causing a #VALUE error as that the thing that stands out that is common. I tried incorporating the FIND function into the formula, but i cannot seem to get the syntax correct. Suggestions? The first 3 characters are numbers that are unique identifiers for the phrase.
Are you sure you wrote the original formula wrong...
I don't think the dashes are the cause of the problem
Do you have the #VALUE error anywhere in any cells of the database?
Maybe post a more "realistic" - no confidential stuff... example showing that you get that error.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
The formula is working. No, there is not any #VALUE errors in the database. Ok, I thought it was the -- however, I think now it is due to the amount of characters in the cell. The phrases are over 255 characters long so it cuts them off in both the database and my file. When I remove the last letter from both my file and the database it pulls the value correctly.
Last edited by sinspawn56; 04-09-2010 at 03:49 PM. Reason: spelling
You will have to abbreviate them then...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
It would be a pain to have to rename the data dump every time I update the file. Could we not use a FIND or FIXED function?
So what is the Find condition? Or Fixed Condition?
Give examples....
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
The excel =FIND and =FIXED.
I know that but what are we "Finding" or "Fixating"?
I.e. what logic are we to use/compare?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Sorry, I misunderstood what you were asking. Could we use one of those formula or a different one to MATCH only the first 5 characters. All of my phrases have at least a 3 digit unique identifier that they start with. It needs to be added to the MATCH($A14,$F$1:$H$1,0)
I have got to hit the road now... so this is a quick trial...
Try:
=INDEX($F$2:$H$7,MATCH(1,INDEX(($D$2:$D$7=B$13)*($B$2:$B$7=$A$13),0),0),MATCH($A14&"*",$F$1:$H$1,0))
in the sample you first posted.. this would look for whatever you type into A14 and find it in the first part of what is in the ranges of F1:H1.
Hope that works.
If not, I can look later tonight or someone else can continue helping you....
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks. If this way doesn't work I will just go ahead and do what was suggested and abbreviate. It is not as troublesome as I had thought. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks