LinkBack URL
About LinkBacks
I am seeking help on a "find, Match and return data" formula and I attach a woksheet which I hope will explain the problem I am seeking help on.
I do hope someone can help and many thanks for looking. Regards.
I am seeking help on a "find, Match and return data" formula and I attach a woksheet which I hope will explain the problem I am seeking help on.
I do hope someone can help and many thanks for looking. Regards.
Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter alone.
In D13
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Regular formula
=INDEX(B3:H3,SUMPRODUCT((B5:H10=B13)*{1,2,3,4,5,6,7}))
or this. It will return blank if there is not match found.
=IFERROR(INDEX(B3:H3,MAX(INDEX((B5:H10=B13)*{1,2,3,4,5,6,7},0))),"")
Last edited by AlKey; 01-25-2015 at 12:39 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Many thanks to both of you for your incredibly prompt replies and both formulas work well except when the "matching" cell H7 is blank and there is no match, I had hoped for a "". One formula gives a #NUM error and the other a false reading of "C" when it should be "D". Any thoughts?
See the second formula in my first post
Sorry yes that's much better but if H7 is "" it returns a 0. Also just realised that each column could vary between 5 cells and 10 in length so is there any way round that, I just choose 6 for the test. Regards
I don't see any issues with 0 in H7. The formula returns blank on my machine. But you can try this:
Go to Options, Advanced, look for a section "Display options for this worksheet", "Show a 0 in cells that have zero value" and uncheck it.
As far as the range of cells and columns you can change it as need it. Just make sure to change the count of columns in {1,2,3,4,5,6,7}
Hi Alan,
I have attached a copy of your file and added the following formula to Cell N8:-
=IF(B13<=6,("A"),IF(B13<=12,("B"),IF(B13<=18,("C"),IF(B13<=24,("D"),(" ")))))
The font colour in Cell N8 is white so you cannot see the result.
Cell D13 simply = Cell N8.
If you want to extend the list simply amend the numbers in the formula.
Hope it works for you.
Regards
peterrc
I am still evaluating all your kind replies but I realise that it makes such a difference that my
"Test" worksheet layout is so different from the "actual" one, I didn't think it would make a
difference but I know now it does so I attach the actual layout and offer my sincere apologies and hope that you can offer the answer,
Try this..........
In AJ4
Please Login or Register to view this content.
Please see attached file with formula in AJ4 and a formula in the helper column AB hidden by custom cell format.
Last edited by AlKey; 01-25-2015 at 06:38 PM.
Again many thanks for all your help, either formula works beautifully on a single cell but I
discovered (I should have given you full details but my original w/sheet is too large) that when I drag & drop the top cell is OK but lower cell formulas don't work and that's simply because there are other cols of data in the way. In other words in col AC2 downwards there is data alongside
which of course "interferes" with the formulas.
I have uploaded a third version showing detail that I should have put in the other sample w/sheets so is there a way to amend the formula or shift the helper col?
Again apologies. Regards Alan
Index test 3 attached.
Have you looked at the file in my post #11? The formula there will do exactly what you want. All you have to do is to pull formula down.
Just add additional codes in AG column.
Yes I have thanks AlKey and I have copied over the formulas from col AB to Z as that's the only empty col I have and I am still trying to figure out the changes I will need to make to them to get it to work.
If you moved to column Z then you need a different formula because VLOOKUP cant look up from the left. User formula below and pull it down.
=IFERROR(INDEX($Z$2:$Z$58,MATCH(AG4,$AA$2:$AA$58,0)),"")
I put the formula that you advised in post #15 into Z2, but it didn't work. Does the original formula in AJ4
IFERROR(VLOOKUP(AG4,$AA$2:$AA$58,2,0),"")
still hold good and if not what code should I put in AJ4 considering the amount of data in the cols in between?
Create a helper column AE and fill with the appropriate Men A, Men B etc.
Then enter this formula in AJ4 and fill down.
Formula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Why in the world do you have these number is the same column as Men A? How do you expect the formula to work with conflicting values?Originally Posted by alan_stephen75@
5-0
5-1
3-2
2-3
1-4
0-5
Thanks for all your help amended my original layout
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks