Hello,
I am trying to create a formula to match different items from different columns.
So I want to be able to put data in G and the columns H:L will contain the formula to look up the values. The values in G can come from any of the columns. I have been doing some research online and I cannot figure this out. I am almost sure it can be done with an array formula.
See attached sample.
Formula.xlsx
Thanks a lot in advance
I'm not sure what "value" you want to show, but if you're just trying to find out if the ID is in the particular column, then use COUNTIF. For example, in cell H2, use:
Copy/Autofill across and down.
Please Login or Register to view this content.
Cheers,
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
Thanks a for the response ConnexionLost,
But I need to bring in the data.
I have created a formula for it but its super long. and on top of that I am repeating it to get rid of the zeros when there is not a match.
Here is the formula:
Formula:
Please Login or Register to view this content.
and this the desired output.
Formula.xlsx
Thanks a lot.
How about this array formula:
Confirm as an array formula with Ctrl-Shift-EnterPlease Login or Register to view this content.
Cheers,
Hi,
This works perfectly. Do you mind explaining how you did it. Just the main idea.
Thanks
Since you were bringing back all the info from the relevant row in the source table, you just needed an INDEX function that knew which row to get. I used an array --($G2=$A$2:$E$21)*(ROW($A$2:$A$21)-1) to identify the necessary row, summing it to get the single number for the INDEX function.
Thanks,
Do you know if there is a row max for this. I tried it with my real data and its not working now
Formula:Please Login or Register to view this content.
I am getting a #Name? error
Sorry about that
I haven't used Excel 365 before, but in the normal Excel 2010 version, it shouldn't be a problem.
I don't see anything wrong with the formula you entered. Assuming you did Ctrl-Shift-Enter.
Thanks ConneXion,
The problem for the error is that there was an error in the resource sheet so the error was carried over.
Now for some reason the data seems to be repeated sometimes on the top. This is happening when its not finding an ID which can also happen.
Thanks
Hi ConnexionLost,
Can you please give solution to my thread https://www.excelforum.com/excel-gen...ml#post4844044.
Thanks,
Naveen.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks