# Get column value based on cell valued with duplicates

1. ## Get column value based on cell valued with duplicates

Hello everyone!

I have this table:

Attachment 636234

**********321------206------207------210------213------214------215------329------216------222***
321---AA---311---------------------------------------------------1--------------------1
206---BB--------------345-----------------------------2-----------------------------------------5
207---CC--------------------------188----------------11-------------------------------2
210---DD---1----------2-------------------397------------------------------1------------------10
213---EE----------------------------------------------157---------------------------------------------------3
214---FF--10----------------------------------------------------227-------------------2--------------------4
215---GG---------------1-------------------7----------1-------------------256--------2--------17---------3
329---HH------------------------------------------------------------------------------254
219---II-----------------3------------------3------------------------------1--------------------193----
222---JJ------------------------------------------------------------3------------------------------1--------236

I am trying to get the row and column values based on a given name from column 1 and 2, like this:
Name: BB (finds row values)
Loc: 206 (finds column values)

Expected Result:
**206------213------2016**
345------2 ------ 5
2
1
3

So far I got this formula: INDEX(\$D\$14:\$M\$14,,MATCH(D23,INDEX(\$D\$15:\$M\$21,MATCH(\$C\$23,\$B\$15:\$B\$21,0),),0))
but it does not work when same numbers are in the row.

Thanks

2. ## Re: Get column value based on cell valued with duplicates

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

3. ## Re: Get column value based on cell valued with duplicates

Thanks.
I just updated the post and attach a mock table.
Now, I am crossing fingers.
Have a nice day

4. ## Re: Get column value based on cell valued with duplicates

You should be able to put this formula in E17 and then copy over and down:

``Please Login or Register  to view this content.``

5. ## Re: Get column value based on cell valued with duplicates

I forgot to mention that the numbers you have in the top row are text, but the numbers in the bottom grid are numbers. You have to make them the same (either make them all text or all numbers) or it won't work.

6. ## Re: Get column value based on cell valued with duplicates

I guess I did not express myself correctly.
The solution you provide works if I know the row and column headers.
But I want to extract them from the table, providing only Column Header and name.
I want the whole row and whole column without blank spaces from the table

Like this:

345-2-5
2
1
3

Sorry for the misunderstanding.

Thanks again for your time and support

7. ## Re: Get column value based on cell valued with duplicates

I think I know what you mean. With an input of a "Loc" and a "Name", you would like the whole "mini-table" to be generated starting at G16 - you want the headers across the top filled in and also the labels down the left hand side automatically filled in and then numbers inside? Are you looking do this without VBA/macros?

8. ## Re: Get column value based on cell valued with duplicates

I don't mind working with VBA.
Yes, I want a "mini-table" to be generated.
Yes, you read my mind, I want the headers across the top filled in and also the labels down the left hand side automatically filled in and then the numbers inside on the "mini-table"

Thanks

9. ## Re: Get column value based on cell valued with duplicates

I don't know how that can be done without using VBA (at least not elegantly).
I'm not an expert in VBA, but I'm sure it would be doable - just a lot of work for me - sorry. Hopefully someone else will read this and have a much better answer!

10. ## Re: Get column value based on cell valued with duplicates

What if I have the values inside the mini-table?
I will need only the headers from the column and labels from the rows.

11. ## Re: Get column value based on cell valued with duplicates

I think I may have something, but I have a question:
Can you upload a file which shows the result grid as it should be if you had the number 7 in Cell D3 and the number 8 in Cell e2?

Or, instead of showing it for BB and 206, show results for DD and 210.

12. ## Re: Get column value based on cell valued with duplicates

Here is the file with the new result given DD and 210:
321 206 210 215 216
DD 1 2 397 1 10
GG 7
II 3

13. ## Re: Get column value based on cell valued with duplicates

OK, so I didn't think it could be done without VBA, but I was able to make it work with just formulas. Like I said, it's not elegant, but it does work.

If you open the attachment and enter any Name and Loc, it seems to build the table as expected. I used some helper rows and columns.
I made the font white for those cells so it looks cleaner, but you can see them in columns starting at N2 and O2, and then in rows starting at D13 and D14.

While the formulas work by just taking the first ones in each row/column and dragging them down or across (including the formulas in the result grid), you'll
have to do some editing if your top table has more rows and columns.

I didn't take anymore time to try and build the formulas so they could be used when the table is bigger, but if this works for you,
you can probably get the idea and expand the formulas to work as needed.

There may be a much simpler solution - maybe using the Aggregate formula, but I don't know how to use that formula yet.

14. ## Re: Get column value based on cell valued with duplicates

Thank you so much!
I just tried it and it works perfectly!!

Thanks for your time and patience.

Have a nice weekend.

15. ## Re: Get column value based on cell valued with duplicates

Happy to help and glad that it works for you.

There are currently 1 users browsing this thread. (0 members and 1 guests)