I am needing some formula help to return a list based on multiple column matching.
For example,
First Name, Last Name, Skill, Selected First, Selected Last, Skill
John, Doe, A, John, Doe, ???
John, Doe, B
Jane, Doe, C
John, Johnson, A
My data validation only returns the first match for John Doe "A". I am needing A, B and C to be place in the list when selecting John Doe.
Data Validation Forumula:
=INDEX(C2:C7,MATCH(1, (D2=A:A)*(E2=B:B),0))
Any help would be greatly appreciated!
Create a Named Range (CTRL-F3) called skill. Use this as the NR formula:
Formula:
Please Login or Register to view this content.
In data validation, select = Skill.
see sheet
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Unfortunately a named range will not work, as the skills will have spaces and special characters.
You might at least have tried it out. See sheet (nothing, except the skills, has been changed). If that doesn't work, reload a REALISTIC sheet, not a pale imitation of the real thing.
You are right, should have posted the real one
Having trouble translating it over, here it is.
On Worksheet column C - select Baseball. On Worksheet column D select 6th/7th/8th. The Home Team and Away Team should only load the teams from the Teams worksheet Where Teams A matches Worksheet C, and Teams B matches Worksheet D.
OK. I'm away now. Back in 12 hr
Thanks man - really appreciate the help!
I was able to get it working mostly right with the following formula (and adding a column where the data was concatenated - column F):
=OFFSET(Teams!$C$1,MATCH(C$2&D$2,Teams!F$1:F$94,0),0,COUNTIF(Teams!$F$1:$F$94,$C$2&$D$2),1)
Now the only issue I am seeing is the values lose their sorting, which would make data entry difficult on a large list.
Attached demos the sort issue.
Your attachment at Post 11 was completely blank. So I used the one from Post 5.
I added a header column and sorted first by column A and then by column B. Now all the relevant values are beside each other. I prefer not to use OFFSET, as it recalculates EVERY time ANYTHING is changed. Like dynamite, OFFSET needs to be handled carefully!!
What is the point of column D? It contains the SAME values as column C. So I based Home & Away on column C. You can delete column D... and column E?? There is, of course, the possibility of selecting the same team in both columns... or the same team twice, further down the page. That can be controlled, too, if you need. However, I have not done so... yet. The DV is applied only on the shaded cells. Try it out. Don't extend it all the way down the sheet, unless you have 1,000,000 lines of data. use a reasonable, future-proof range.
I changed your named range for Level, too, as it didn't seem to be working.
Another useful feature might be to replace the fixed ranges in the DV with dynamic ranges, which will adjust automatically if/when teams and/or levels are added. Can that happen often?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks