LinkBack URL
About LinkBacks
Hello Excel Forum.
I'm to trying to get this worksheet where a list of values will return based on the same customer on different sheet.
How do I go about this?
I did try vlookup, but gives the the same value instead of multiple values.
Return several values based on 1 criteria
Hello Excel Forum.
I'm to trying to get this worksheet where a list of values will return based on the same customer on different sheet.
How do I go about this?
I did try vlookup, but gives the the same value instead of multiple values.
Last edited by kenjcd; 05-14-2019 at 10:58 PM.
Because the value you are searching for "George" is not unique in your lookup table, you will only find the first match from top down.
You can accomplish this with INDEX/MATCH array formulas. Array formulas are committed by holding ctrl+shift and hitting enter. In B4 on Receipt tab:
In C4 on Receipt tab:
Please Login or Register to view this content.
And finally, if you want auto numbers in column A on the Receipt tab, use this non-array formula (entered normally):Please Login or Register to view this content.
Does that give you the results you're looking for?Please Login or Register to view this content.
If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.
If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.
Good day Melvosh!
Thank you so much for your help.
Formula only seem to work with George but not with other values
Last edited by kenjcd; 05-14-2019 at 03:32 AM.
I didn't test the "number generator" formula. Let me work on that. If you manually put in numbers starting at 1, it will work.
You can use this in column A on Receipt:
Be aware, it relies on the formula starting in row 4. If it needs to start elsewhere, change the 3 in the formula to one less than the row it starts in.Please Login or Register to view this content.
Hi Melvosh.Originally Posted by Melvosh
You can use this in column A on Receipt:
Be aware, it relies on the formula starting in row 4. If it needs to start elsewhere, change the 3 in the formula to one less than the row it starts in.Please Login or Register to view this content.
Correct me if I'm wrong. Say I want to add new customer, all I need to do is change the formula e.g $B$2:$B$20 (previously $B$2:$B$9) enter as an array and rest follow right?
toPlease Login or Register to view this content.
I don't know where I'm doing it wrong but, it wont show the new value.Please Login or Register to view this content.
Perhaps put this on B4 and copied down and cross until blanks :
=IFERROR(INDEX(Sheet1!B$2:B$100,AGGREGATE(15,6,(ROW(Sheet1!B$2:B$100)-MIN(ROW(Sheet1!B$2:B$100))+1)/(Sheet1!$A$2:$A$100=$C$1),ROW(A1))),"")
For Numbering use this:
=IF(B4="","",COUNTA($B$4:B4))
Noted.
Thank you azumi/Melvosh/kersplash
Last edited by kenjcd; 05-15-2019 at 01:35 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks