Hi people,
If you've the time please have a look at the image I've attached.
I'm trying to have the table on the right match the first two columns to display the customer name taken from the table on the right.
Can anyone please help?
Hi people,
If you've the time please have a look at the image I've attached.
I'm trying to have the table on the right match the first two columns to display the customer name taken from the table on the right.
Can anyone please help?
Hi and welcome to the forum
For future reference, Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)
Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
having said that, try this, copied down and across. Your profile says 2003, so....
=IF(ISERROR(INDEX($C$2:$C$8,MATCH($E2,$B$2:$B$8,0),MATCH(F$1,$A$2:$A$8,0))),"",INDEX($C$2:$C$8,MATCH($E2,$B$2:$B$8,0),MATCH(F$1,$A$2:$A$8,0)))
If you have 2007 or later, you can shorten that to this...
=IFERROR(INDEX($C$2:$C$8,MATCH($E2,$B$2:$B$8,0),MATCH(F$1,$A$2:$A$8,0)),"")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
That works a treat, thank you so much.
And apologies for just posting an image, it didn't even occur to me that I should do that. I will remember to in the future.
Thanks again.
you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)
Ok so it almost works, it will place the names on the right row but not column, mucked around with it for a while and can't see where the problem is. Although as I wasn't bright enough to come up with the formula myself im not that surprised.
try in f2
=IF(ISNA(INDEX($C$2:$C$8,MATCH(F$1&$E2,INDEX($A$2:$A$8&$B$2:$B$8,0),0))),"",INDEX($C$2:$C$8,MATCH(F$1&$E2,INDEX($A$2:$A$8&$B$2:$B$8,0),0)))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
This array formula** entered in F2:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($C$2:$C$4,MATCH($E2,IF($A$2:$A$4=F$1,$B$2:$B$4),0))))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy across to N2 then down as needed.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks for the idea's guys but im not having any luck with those, although it could be because trying on libra office at the moment, will try them again then I get back to my ms2003 system at home.
But I think FDibbins post may have been closer to the mark.
I simple cannot see why its not checking the cottage name as well as the date.
Here's your file with the formula from post #7 implemented.
Awesome, thank you tony. I can happily mark this one as Solved
Ok sorry one last question. Im assuming the "zzzzz" denotes a text string, what should I replace that with for a numeric string?
not sure what you mean where would the numeric string be? heres #6 working
Sorry ignore that, basicly for whatever reason if I replace the name with a string of number's it won't display it, unless it also contains a letter.
If you're needing to lookup numbers:
=IF(ISNA(MATCH($E2,IF($A$2:$A$4=F$1,$B$2:$B$4),0)),"",INDEX($C$2:$C$4,MATCH($E2,IF($A$2:$A$4=F$1,$B$2:$B$4),0)))
Still array entered.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks