VLookup 1st, 2nd, 3th value?

LH-1030

New Member
Joined
Feb 4, 2005
Messages
21
Hello all!

I have a question, but that what this board is made for no?!

I'm working with 2 sheets, in the first I have al the information about the products used. Different columns are indicated, eg. Potency, lot N° 1, lot N°, Status, Date,...

On the second page I would like to sort everything in different categories, I prefer to make a distinction between the potency, there are 4 (1-2-3-4). So my second page would look like: products with potency 1 and then 3 columns which indicate lot N° 1, lot N° 2 and Date (copied from the first page).

I hope you can imagine what's happening...

The formulas used to copy the contents from the first page, I managed to work out: =IF(ISNA(VLOOKUP($C$4;'Sheet 1'!$B$3:$H$500;2;FALSE)=TRUE);"";(VLOOKUP($C$4;'Sheet 1'!$B$3:$H$500;2;FALSE))).

And now the problem: I always get the same result (row from sheet 1) so I'm looking for a possibility to search the second potency value 1, the third and so on.

Could someone help me?

Thanks a lot!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Try concatenating the product and the potency, then you will get a unique value for the vlookup to evealuate.


HTH


:cool:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
If it's one time operation, invoke AutoFilter and copy/paste the results to the destination sheets.
 

LH-1030

New Member
Joined
Feb 4, 2005
Messages
21
I guess that won't work?!

If I combine the values of two rows, I get a unique value indeed, but the Vlookup function can't look for that unique value I guess? Can you define a function in Vlookup to look for a part of the information in a cell?

Or what should become of my Vlookup function in order to show all the rows from sheet 1 that have potency 1 in their row?

The more I think about it, the more difficult it becomes :(

Thanks
 

LH-1030

New Member
Joined
Feb 4, 2005
Messages
21

ADVERTISEMENT

Sorry, but I don't know how to use the autofilter function on the second sheet when the values from the first sheet should be sorted?
 

Ed B. Delen

Board Regular
Joined
Feb 16, 2005
Messages
78
Hi

Concatenate & vlookup will do...insert 1 column before your data to make it the first column... then concatenate the two columns, in the lookupvalue type the combined the two criteria.

try

regards,
ed
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

LH-1030 said:
Sorry, but I don't know how to use the autofilter function on the second sheet when the values from the first sheet should be sorted?

Apply AutoFilter on Sheet1 then copy the result you see and paste the copy in the destination sheet.
 

LH-1030

New Member
Joined
Feb 4, 2005
Messages
21
That's my problem,
I only know the potency for sure, the lot number depends.
So if I want to show al the lots with potency 1, I don't know what the lot numbers would/could be.

I tried the autofilter, works great but that way I can show only one potency per sheet, is there a possibility to show the 4 potencies "filtered" on one sheet?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
LH-1030 said:
That's my problem,
I only know the potency for sure, the lot number depends.
So if I want to show al the lots with potency 1, I don't know what the lot numbers would/could be.

I tried the autofilter, works great but that way I can show only one potency per sheet, is there a possibility to show the 4 potencies "filtered" on one sheet?

Try to OR using the Custom option.
 

LH-1030

New Member
Joined
Feb 4, 2005
Messages
21
Using Autofilter only allows to sort for one potency per sheet, I would lke to sort the 4 potencies on the same sheet...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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
Top