VLookup 1st, 2nd, 3th value?

[LH-1030]

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!

 

[SteveO59L]

Try concatenating the product and the potency, then you will get a unique value for the vlookup to evealuate.


HTH

 

[Aladin Akyurek]

If it's one time operation, invoke AutoFilter and copy/paste the results to the destination sheets.

 

[LH-1030]

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]

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]

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]

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]

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]

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]

Using Autofilter only allows to sort for one potency per sheet, I would lke to sort the 4 potencies on the same sheet...