index / match lookup formula not working

[kevinu]

I looked this formula up but im getting it wrong somewhere. I'm trying look up the values from the array on sheet2 c3:p173. I use the values in column A and column B on sheet1, if they match the values on sheet2 column A and B then it returns the values in the array on the same row of the match. At least thats what im trying to do.

[Glenn Kennedy]

In C2, an array formula, copied across & down:

=INDEX(Sheet2!C$3:C$173,MATCH(1,($A2=Sheet2!$A$3:$A$173)*($B2=Sheet2!$B$3:$B$173),0))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[kevinu]

I see what you did, so i have to use this formula in each cell not on a row of cells. There's not way to have it return the row instead of cell by cell? Just curious.

[Glenn Kennedy]

No. You set it up in once cell and drag across and down. Your original formula also did not have appropriate anchoring to lock cell references correctly ( the $ s) and didn't specify an exact match (,0 at the end of the MATCH statement).

[kevinu]

ok that makes since. Thanks. I have a second question if you have time. The data I used comes in 2 forms. How would i adjust the formula if the data used to match was in one cell on one sheet and on 2 cells on another sheet. I'll adjust the example to show what i mean.

i can use trim to separate them but it would require a separate formula and workspace. If i can do it with one formula that would be nice.

i tried this but it didnt work

Please Login or Register  to view this content.

[Glenn Kennedy]

1. You changed the cell references in INDEX from INDEX(Sheet2!C$3:C$173 back to INDEX(Sheet2!C3:P173. That is incorrect and it will not work.

2. You left out all of the $ signs in the formula that I used to lock the cell references as you copy/paste. That is incorrect and it will not work.

3. In sheet 1 column B, all of the entries end in LE. None of the entries in columns A or B in sheet 2 contain LE. So there are no matches to be found.

4. The values to the left of the - in sheet 1 column B are sometimes to be found in sheet 2 column A (e.g. FP1) sometimes in column B (e.g. Cz) and sometimes in both (F3, F4, etc).

Please amend sheet 1 column A and repost, explaining which part of the code applies to sheet 2 column A and which part to sheet 2 column B.

[kevinu]

Glenn, Sorry I copied the wrong data. Here is the way it should look. On sheet 1, column B i have 2 variables in one cell such as fp1 - p4. It needs to look at sheet two and look at column A and B to match. I know how to use Left and Right to get the first or second part but i cant seem to find out how to use in in the formula that also pulls the cell data back. Again sorry for the messed up example. Thanks for you help.

[Glenn Kennedy]

In C2, an array formula, copied across and down:

=INDEX(Sheet2!C$3:C$173,MATCH(1,(Sheet2!$A$3:$A$173=TRIM(LEFT(Sheet1!$B2,SEARCH("-",Sheet1!$B2)-1)))*(Sheet2!$B$3:$B$173=TRIM(MID(Sheet1!$B2,SEARCH("-",Sheet1!$B2)+1,255))),0))

or (if you prefer) an ordinary formula, copied across and down:

=INDEX(Sheet2!C$3:C$173,MATCH(1,INDEX((Sheet2!$A$3:$A$173=TRIM(LEFT(Sheet1!$B2,SEARCH("-",Sheet1!$B2)-1)))*(Sheet2!$B$3:$B$173=TRIM(MID(Sheet1!$B2,SEARCH("-",Sheet1!$B2)+1,255))),),0))

the attached sheet contains the ordinary formula.

[kevinu]

thanks that did the trick

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.