Picking data from a table

Posts
4
Registration date
Wednesday October 4, 2017
Last seen
November 10, 2018
-
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!



See more 

Your reply

1 reply

Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
0
Thank you
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
Respond to TrowaD