Hi,
I have a list of data in which I want to lookup a range from another range and pull data accordingly.Currently,I am using below formula,
Lookup data of Result sheet is :D5:D8.
Please Login or Register to view this content.
Pls find in attachment sample data set.
In B5 copied across and down:
=INDEX(Data!A$4:A$11,MATCH($D5,Data!$C$4:$C$11,0))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I know this formula but would like to use Filter function.
The problem is that you can't filter a list in a different order.
IN B5 then copied down.
=FILTER(Data!$A$4:$B$11,Data!$C$4:$C$11=Result!D5,"Not found")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
He doesn't want to copy down - that's the problem. He wants it to SPILL.
Yes, exactly what I am looking for.
Last edited by AliGW; 05-17-2022 at 06:34 AM. Reason: PLEASE don't quote unnecessarily!
Enter D, G, B, M in E5:E8
In B5 enter
=FILTER(Data!$A$4:$C$11,COUNTIF(Result!$E$5:$E$8,Data!$C$4:$C$11)=1,"Not found")
It will automatically Spill.
Last edited by kvsrinivasamurthy; 05-17-2022 at 08:14 AM.
Try
=LET(z,Data!A4:C11,l,D5:D8,FILTER(FILTER(SORTBY(z,MATCH(INDEX(z,,3),l,)),SEQUENCE(ROWS(z))<=ROWS(l)),{1,1,0}))
Thanx Bo_Ry,
Your post #9 worked as expected.
I am getting a Value Error when I have inserted two columns in 'LIST' Sheet.Can you suggest me what will be the revised formula in this case.
Pls see what is B5 #Value!.
=LET(z,LIST!A4:E11,l,D5:D8,FILTER(FILTER(SORTBY(z,MATCH(INDEX(z,,5),l,)),SEQUENCE(ROWS(z))<=ROWS(l)),{1,1,0,0,0}))
should do it.
Last edited by Glenn Kennedy; 07-28-2022 at 08:31 AM.
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
Thanx Glenn!
Can you let me know what does " l " means ?
It's a lower case L. Bo_Ry used it as a short name to refer to the LOOKUP range (D5:D8)
Thanx
Hi,
Let me brief,
LAX worksheet from C6:R972 is the data range & total column are 16
Match column is A3:A1752 against worksheet LAX C6:C972
would like to get the result from E,K,Q,R column
Kindly let me know where I am wrong in this syntax.Couldn't share the sheet due to confidentiality of data.Please Login or Register to view this content.
Why not test it in your dummy sheet? After that you can copy the working formula to the "confidential" sheet.Kindly let me know where I am wrong in this syntax.Couldn't share the sheet due to confidentiality of data.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
PFA the demo file.I have used the range as per mine actual.Only few data I have shown with formula as mentioned.Momentarily,it is displaying wrong result.
Still eagerly waiting for the response.
I don't want to have to go through 18 posts to follow this. In your most recent attachment...
1. Which cells are showing the wrong answer?
2. What is the correct answer?
3. Why?
Yes, Kindly,do not go through entire 18 Post.Below, information is suffice.
Plz find in attachment.Formula has been used based on above previous formula format.I thought it was versatile but when testing in one of the cases it has shown wrong result.
I have enclosed a file with 3 worksheets.They are-Result,LAX and Correct Answer.
Formula is applied in Result worksheet at H3 and data is pulled below and across by Matching range of A3 and below with that of LAX worksheet C6 and below.For, simplicity,I have highlighted the header with yellow which column to extract from LAX worksheet to Result worksheet.
My actual results/Expected result is in 3rd Worksheet 'Correct Answer'
I want in the same format formula as only syntax is to be readjusted but how?Don't know.
I think I just I have answered all your questions.
Last edited by paradise2sr; 09-06-2022 at 11:14 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks