Using nested Vlookups or Index/match?

[bakera8]

I am trying to use the data from sheet 1 to populate the cells in the sheet 2. The data I need to match is the first column of sheet 1 and the first column of sheet 2, and I also need row 1 of sheet 2 to match column 2 of sheet 1. I need this data to output column 3 of sheet 1 in cell C13 of sheet 2. I have tried nesting Vlookups, or using Index/match but have not been able to accomplish this yet. What is the best way to do this? The data in sheet 1 can be rearranged if needed.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Gregb11]

Try this in Cell B2 and copy down and across:

=IFERROR(INDEX(Sheet1!$C$2:$C$88,MATCH(Sheet2!$A2&Sheet2!B$1,Sheet1!$A$2:$A$88&Sheet1!$B$2:$B$88,0)),"")

[bakera8]

Isn't this formula is combining the two numbers from A2 and B1 into one number rather than separately searching for them? When I ran the calculation error box that is what it showed it was doing

[Glenn Kennedy]

The biggest problem you have is that the column and row headers in sheet 2 are stored as TEXT, whereas in sheet 1 they are numbers.

Look for the little green triangles in the corner of the cells. That's alerting you to a problem. Select the first one in column A (and then repeat in row 1) Drag across. Click on the icon with the black exclamation mark inside the yellow diamond inside the white square and select convert to number. Then....

this formula works:

=IFERROR(INDEX(Sheet1!$C$2:$C$88,MATCH(1,INDEX((Sheet1!$A$2:$A$88=$A2)*(Sheet1!$B$2:$B$88=B$1),0),0)),"")

[bakera8]

Thanks Glenn, I always forget about the formatting.. That formula did work after I converted everything to number. I was also missing the extra index inside the match function when I tried to do it but it makes sense now

[Glenn Kennedy]

The little green triangles annoy the hell out of me... but they have their uses!!

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

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

[Gregb11]

For the record, my formula in Post #3 above gives the exact same results... :-)

[Glenn Kennedy]

It does, if you convert the text to numbers AND if you enter it as an array formula. Hence, it didn't work. it's amazing how quickly you forget about array formulae when you shift to O365...

[Gregb11]

it's amazing how quickly you forget about array formulae when you shift to O365

Aint that the truth :-)

[AliGW]

Agreed!!!