vlookup column 2 but return column 1

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
300
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Book1
ABCDEF
15ab10
210b
315c
420d
525e
630f
735g
Sheet4
Cell Formulas
RangeFormula
F1F1=VLOOKUP(E1, CHOOSE({1,2}, $C$1:$C$7, $A$1:$A$7), 2, 0)
 
Upvote 0
You can't do that with vlookup (actually, it is possible, but pointless when there are far simpler methods).

It should be something like =IFERROR(INDEX(D:D,MATCH(B2,C:C,0)),"")

But as you have named all 4 columns as a single range, you're making it slightly less simple.

=IFERROR(INDEX(STOCK,MATCH(B5,INDEX(STOCK,0,2),0),1),"")
 
Upvote 0
Hi,

Thanks for reply. I worked out that I need to use Index and match and have it working on 1 at a time selection however i'm now trying to manipulate it to have 2 options using multiple ifs,

=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)),""))

This is my code with both in, individually they work, but now I have put them together they don't, where have I gone wrong above please?

Fletch
 
Upvote 0
The formula looks fine, what is it doing wrong and what are you expecting it to do?
 
Upvote 0
1590311910677.png


The above is basically my 2 data areas, I need to be able to search from my main screen under asset or Tag from one selection area.
1590312076148.png


The formula I sent only works on Asset and not tag
1590312222307.png


I'm missing something simple I know I am.

Thanks

Fletch
 
Upvote 0
p.s. at the moment I am only trying this out with Current Stock as shown above, I was hoping to add Gone to site stock in my search also.
 
Upvote 0
@rfletcher35
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

We cannot see row numbers (try using XL2BB) so what is in R33 and B5?
 
Upvote 0
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)),""))
 
Upvote 0
Hi I will do that, I currently work on 365 but this project I am working on is for the NHS and they currently use Office 2010
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top