Picking data from a table

Closed
AdrianHowland Posts 4 Registration date Wednesday October 4, 2017 Status Member Last seen November 10, 2018 - Updated on Nov 11, 2018 at 02:31 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 12, 2018 at 11:39 AM
Hello, I am going to apologise once again in advance as I am terrible at coming up with the right question when it comes to excel formulas hence why I am here.

Basically, I would like to create a search function for a table. The table would have two rows that I would like to search i.e. it would be names along the top horizontal row and characteristics down side vertical row.

I would like to be able to write a [Name] in one cell and [characteristic] in another cell and in the third cell (results cell) it would come up with the data that we find in the table under those two inputs.

I realise this would be unbelievably easy to just look up the table yourself and type in the Cell number but I would like it so that the person searching would just need to type in the real data to find the result.

I really hope this makes sense, I realise I do not know all the terminology so please do let me know if I can explain this a bit better? I think perhaps due to my not knowing how to describe it I have not been able to find what I am looking for on google.

Any help would be greatly appreciated!



Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 12, 2018 at 11:39 AM
Hi Adrian,

My understanding of your query is that you have a table with names in row 1 and characteristics in column A. When you put a name in one cell and a characteristic in another cell, you would like to retrieve the matching value from the table.

If this is correct then have a look at the solution below:
Let's say the table has a range from A1 to E5.
You enter the name in J1 and the characteristic in J2.
To get the result from the table use:
=INDIRECT("R"& MATCH(J1,A1:E1) & "C" & MATCH(J2,A1:A5),FALSE)

Best regards,
Trowa