Index Match a range

[Russk68]

Hi All

I want to index a column and match a criteria in multiple columns.

I want to index column A and find a match in columns B:F
I gave this a try =INDEX(A1:A20,MATCH(G1,B1:F20,0))

Thanks!

 

[mole999]

can you post a table of those values, and pick an answer you want to get from the data

 

[Zack Barresse]

You'd need an array formula for the multiple criteria match. Unless you're wanting to sum a numerical value, however, if there were multiple matches it would sum the range specified instead of just returning the first index match.

Array example (confirmed with CTRL+SHIFT+ENTER instead of just ENTER):

=INDEX(range,MATCH(item1&item2,item1Range&item2Range,0))

All ranges in the above must be of identical size.

If there aren't multiple criteria matches, check out the SUMIFS function, which is non-array entered.

 

[Russk68]

Hi Zack
I only need to match 1 item but from multiple columns.

I want to:
index column A1:A20
Match G1
Find match in Columns B1:F20

Thanks!

 

[Russk68]

Hi
Off topic here but I don't know how to paste images here. I've tried several time over the years with links provided on how to do it and find it embarrassing that I never figured it out.

I posted what I hope is a clearer explanation.

Thank you!

 

[Burrgogi]

There's no feature on this forum to upload images. (not that know of anyways.) Your best bet is to create an account at an image hosting site such as www.imgur.com and upload your screenshot there. Once done, you can provide the link for the image in your posting.

In this case, I don't see why you're looking to create a screenshot for your query here. Read and re-read Mole999's question to you and then provide a sample as requested. You just might get a better answer.

 

[Russk68]

OH!
Got it
Thanks

 

[Russk68]

Hi
I provided a screenshot.
I found the formula in a search and changed it as needed but isn't working as I would expect.

The 2 blue cells on the right show the criteria and the result.

The result is Job 2 when I would expect the result to be Job 1.
https://www.dropbox.com/s/l5k61bnabpde3w2/Screen Shot 2019-01-06 at 1.22.44 PM.pdf?dl=0

Thank you!

 

[Russk68]

Hi Zack
I've been stuck on this for 2 days and I was hoping that you can assist me further.
I attached a different screen shot that I think will explain what I'm trying to do a little better.

I'm trying to Index Match 2 criteria with a special circumstance. Im indexing column B31:B100 to return the job name BUT I'm trying to match the 2nd criteria in B18:B29.

My layout is a little unorthodox which is causing me the issue. Column B31:B100 is the job name and B18:B29 are the names that are assigned to the job which is creating the ranges to be different size.

Hopefully this screen shot will help.
https://www.dropbox.com/s/vug30nlzpmij8ke/Screen Shot 2019-01-06 at 9.16.11 PM.pdf?dl=0
Thanks!

 

[Aladin Akyurek]

A pdf file is not Excel readable, therefore not that useful.