Indexing. Return value from a certain Row and Column.

**nogstai** · 03-11-2013, 07:40 PM

I have from my raw data, employees with different metric scores and values.

Due to the crappy export system, several rows are for one associate and the Metric Names are in one column.
I separated it in two sheets, so I can just call back the data from there. Unfortunately I'm having problems.

I created this table:

Table1.JPG

And this is what the raw data looks like:

Raw1.jpg

I would need help in creating the formula where it will lookup the name in a row (ie. Arredondo, Alejandro), together with a metric name from the colums (ie.Metric Value). So I can just have one row per associate.

I tried using the Index forumla, but I failed:

=INDEX(DrDishStack, MATCH($B1, INDEX(DrDishStack,,1),0), MATCH($B2, INDEX(DrDishStack,12,),0))

DrDishStack is the table name.

if you want I can e-mail you the testdata file I'm using to play around with this spreadsheet, so you can actually see what was needed. Or I attached it here, just not sure if I attached it right. lol

help will be very much appreciated.

THANK YOU!!

**benishiryo** · 03-11-2013, 08:28 PM

hi nogstai, welcome to the forum. would be good if you can manually key some answers so that we know our solution's on the right direction. and i personally don't like to range up the whole column, so maybe this in B3:

Formula:

Please Login or Register  to view this content.

**nogstai** · 03-11-2013, 08:49 PM

I just tried it out. And your formula works.

Can I ask, how it was done? Or a detailed explanation. If it's not too much bother. I would like to learn it by heart.

Also, I uploaded the file that was manually answered. and the other table is not yet. But when I tested to the other cells, it's working. Double-checked and everything.

THANK YOU VERY MUCH

**wraithlet** · 03-11-2013, 08:53 PM

You can also use a sumifs formula to achieve similar results, its allows you get the sum a column where multiple conditions such as name=VALUE1 and metric name=VALUE2 are met.