vLOOKUP OR INDEX

[cogswel__cogs]

I am being sent table in followoing format
I want to lookup based on name and color.
Do I use a vlookup with offset?
Index and match?

ColA COLB

John
Red 12
Blue 324
Pink 34
Paul
Green 1234
Red 1121

Any help is always greatly appreciated

 

[oldbrewer]

John

John

John

John

Red

12

John#Red

12

John

Blue

324

John#Blue

324

John

Pink

34

John#Pink

34

Paula

Paula

Paula

very easy to put in 2 helper columns

Paula

Green

1234

to get the name by every row

Paula#Green

1234

Paula

Red

1121

Paula#Red

1121

then to append name plus # to color

type in name

John

type in color

Blue

search for

John#Blue

quantity

324

formula

=OFFSET($K$1,MATCH($G$18,$K$2:$K$7,0),1)

<colgroup><col span="2"><col><col span="7"><col><col span="2"></colgroup><tbody>
</tbody>

 

[cogswel__cogs]

Was trying to do it without additional columns as I am going to get as a feed often,
Wound up using indirect to rewrite the table array. Not sure if it is best way but seems to be working.


VLOOKUP($D26,INDIRECT("table!A"&MATCH($C26,table!$A$1:$A$106,0)&":g106"),7,FALSE)

 

[LearningByError]

I'm a bit confused but these are the assumptions I'm making. 1.) You have a column with only names in it. 2.) You have another column with colors in it. 3.) Another column with the return values. If these are all true then the code below will work, just replace the descriptions with the cells they are asking for.

IF(MATCH(CELL WHERE NAME TO LOOK UP IS LOCATED,CELL RANGE WHERE ALL NAMES ARE LOCATED,0)index(CELL RANGE WHERE THE VALUE YOU WANT TO RETURN IS LOCATED,MATCH(CELL WHERE COLOR IS LOCATED, CELL RANGE WHERE COLORS ARE LOCATED,0))

 

[cogswel__cogs]

No
The name comes in same column as color.
Color fields for particular name below name with data in next column.

 

[oldbrewer]

John		John
Red	12	John
Blue	324	John
Pink	34	John
Paula		Paula
Green	1234	Paula
Red	112	Paula
	your data is poorly laid out for analysis
	this helper column - which can be hidden away
	which is generated by formula
	makes life much easier
	so if you want
	John
	Blue
	324
	=SUMPRODUCT((C2:C8=B22)*(A2:A8=B23)*(B2:B8))

<colgroup><col span="9"></colgroup><tbody>
</tbody>

 

[cogswel__cogs]

Yes the data comes in horribly. It is how they are going to give it to me, though I offered to fix for them.
I will look into way suggested see if it works quicker than solution I came up with.
Many thanks.
The issue might be, that I really do not know the difference from all the names and colors. And the name rows actually have data in column B as well.
I do know when I get data what is what except for the fact that color for the name will be below.
I will always be looking to pull (a name and a color) that's why I was hoping to drop down in the table and then pull from there. The two fields are actually departments and locations. And the departments share locations often.
And there should always be some result for the department/location if I am making an inquiry so that is why I am not concerned about pulling past the departments data. Just do not want to pull location before I reach the departments data.

Many thanks for the help.