Forum Discussion
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 value with Vlookup is easy enough, but then I want to find the column # for cells containing V and H and return their respective values from row 1 (cells in yellow). Where V and H can also be different depending on the line. Any help on this would be much appreciated.
- Not understanding your question.
V and H in different cells relates different value in row1, you'll need unique formular for each.
If you want to find the column title which has marked "V", use follows:
A2 =OFFSET($C$1,0,MATCH("V",D2:V2,0))
- where MATCH("V",D2:V2,0) is the column number in D:V where you fond "V", if found in D2 it's 1, if found in E2, it's2, etc.
- where OFFSET($C$1, rowoffset=0, coloffset=1) returns value of range C2, etc.
If you want to find the column title which has any value, use follows:
A2 =OFFSET($C$1,0,MATCH("*",D2:V2,0))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?
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)
