Forum Discussion

MS_excel2022

Copper Contributor

Dec 27, 2022

Multiple lookup and references

Hello I'm trying to find a way without using VBA (not very good at it) to find multiple reference. In the table below the red rectangle will change as I find the value column A. Finding the v...

excel

Formulas and Functions

MS_excel2022

Copper Contributor

Dec 27, 2022

XXplore

Thanks for the formula. I figured I would need MATCH and OFFSET, was not sure how to use them. I will be using different formula for V and H.

I get the result I need with the first formula. However D2:V2 can be D10:V10. depending which row I need to look at.

I managed to get the right start and end to the range by using:

"D"&MATCH('sheet1'!A632,A1:A1500,0)

"V"&MATCH('sheet1'!A632,A1:A1500,0)

'sheet1'!A632 is the value I need to match on the other sheet

Once I replace D2:V2 by those formulas, it does not work. Is there a way to make D2:V2 follow the row I need to match?

XXplore

Brass Contributor

Dec 28, 2022

MS_excel2022

I'm not fully understand your question,
But If you lookup a value from A1:A5000 which is a column,
MATCH('sheet1'!A632, A1:A1500,0) will return the row number of first matched cell.

So you would change the formular to =OFFSET($C$1,MATCH('sheet1'!A632, A1:A1500,0),0)
if MATCH returned 3, you will got an offset of 3 rows 0 column from Range("C1"), which is value of Range("C4")

Description: offset(reference, rows, columns, optional height=1, optional weight=1)