vlookup column 2 but return column 1

[rfletcher35]

I'm adding a search to my workbook based on a drop down list of 4 criteria. I've managed to pretty much do this using the offset command in my formulas. But if my VLOOKUP searches on columns 2,3,or 4 how can I do it so it also returns the value in column 1 in the cell I need?

=IFERROR(VLOOKUP(B5,OFFSET(STOCK,0,1),1,0),"") is what I have that returns what I have searched for in column2 of my range
What do I need to return the value of column 1 that corresponds with the data in column 2?

Any ideas please

Fletch

 

[rfletcher35]

From the screen caps, the only thing that looks at though it might be wrong is one of the references in bold below, should both be the same?

=IFERROR(INDEX(Data!$J$4:$J$2000,MATCH(R33,Data!$K$4:$K$2000,0)),IFERROR(INDEX(Data!$J$4:$J$2000,MATCH(B5,Data!$L$4:$L$2000,0)),""))

Yes I did see that after posting, they should both be R33 and I have changed that but it still produces nothing unless I have the Asset in the box

 

[rfletcher35]

Yes I did see that after posting, they should both be R33 and I have changed that but it still produces nothing unless I have the Asset in the box

I even tried CTRL+SHIFT+ENTER to make it an array formula but that made no difference

 

[Peter_SSs]

Your formula works for me (different sample data)

20 05 24.xlsm
	J	K	L
3		Asset	Tag
4	7018	a	h
5	7019	b	i
6	7020	c	5X7H322
7	7021	d	k
8	7022	e	l
9	7023	f	m
10	7024	g	n
Data

20 05 24.xlsm
	R	S
33	5X7H322	7020
Lookup

Cell Formulas
Range	Formula
S33	S33	=IFERROR(INDEX(Data!$J$4:$J$2000,MATCH(R33,Data!$K$4:$K$2000,0)),IFERROR(INDEX(Data!$J$4:$J$2000,MATCH(R33,Data!$L$4:$L$2000,0)),""))

 

[Peter_SSs]

Could it be that your Tag values have trailing spaces or other non-visible characters. Try over-typing the value from R33 manually into one of the column L cells (or copy/paste)

 

[rfletcher35]

Your formula works for me (different sample data)

20 05 24.xlsm
	J	K	L
3		Asset	Tag
4	7018	a	h
5	7019	b	i
6	7020	c	5X7H322
7	7021	d	k
8	7022	e	l
9	7023	f	m
10	7024	g	n
Data

20 05 24.xlsm
	R	S
33	5X7H322	7020
Lookup

Cell Formulas
Range	Formula
S33	S33	=IFERROR(INDEX(Data!$J$4:$J$2000,MATCH(R33,Data!$K$4:$K$2000,0)),IFERROR(INDEX(Data!$J$4:$J$2000,MATCH(R33,Data!$L$4:$L$2000,0)),""))

I just cut and paste your formula in and it does work, I can only assume there was a small difference in mine somewhere?

Thanks so much for your help Peter

Fletch

 

[Peter_SSs]

I just cut and paste your formula in and it does work

Good news.

I can only assume there was a small difference in mine somewhere?

All I did was copy/paste your formula from the forum to my sheet and change B5 to R33. Perhaps you didn't post the exact offending formula from your sheet?

Anyway, main thing is that it is working now.

 

[rfletcher35]

I just cut and paste your formula in and it does work, I can only assume there was a small difference in mine somewhere?

Thanks so much for your help Peter

Fletch

Any ideas the best way to do this using both sets of data?

 

[Peter_SSs]

Can you explain how both sets of data relate (or not) to each other?

What, exactly, are you trying to do?

 

[rfletcher35]

Sorted it, Bit messy but none the less sorted it.

=IFERROR(INDEX(Data!$J$4:$J$2000,MATCH(R33,Data!$K$4:$K$2000,0)),IFERROR(INDEX(Data!$J$4:$J$2000,MATCH(R33,Data!$L$4:$L$2000,0)),IFERROR(INDEX(Data!$Q$4:$Q$2000,MATCH(R33,Data!$R$4:$R$2000,0)),IFERROR(INDEX(Data!$Q$4:$Q$2000,MATCH(R33,Data!$S$4:$S$2000,0)),""))))

The first set of data is stock that we have, the second set is what has already gone to site.

 

[Tom.Jones]

Hi rfletcher35,

You get a lot of answers for same question, here:
extracting data from A11:A15