vlookup problem

[TheIntern]

Hello everyone

Is it possible to use vlookup without sorting the colums in ascending order?
I need data from another sheet transfered without changing the order...any ideas?

Thanks in advance,

The Intern

P.S.: Sorry for the double post...how can you delete a post?^^

 

[Haseeb Avarakkan]

Hello,

You can use the last option to FALSE or 0. This will look for the exact match. Not necessary to sort the data. If the table_array doesn't have the exact match of the lookup_value, will return #N/A. If you can provide a sample data would be better.

 

[TheIntern]

Thanks for your quick reply!
The problem is the data is confidential company property, so I am afraid I can't publish it.
I try to describe it in more detail.
The data I want to transfer to the other sheet is the inventory stock. It is linked to the material number in another sheet. If I try to use vlookup without changing the order ( the sheet will be potected, so the users won't be able to sort) with false or 0 everything is #N/A (or "" in my case,see formula below).
Is there another excel formula, which suits better?

I use this formula:

=IF(ISNA(VLOOKUP(C50;'sheet1'!$BH$12:$BI$1518;2;FALSE));"";(VLOOKUP(C50;'sheet1'!$BH$12:$BI$1518;2;FALSE)))

C and BH contain the material numbers (some are blank between the numbers)
BI contains the numbers I want to look up

 

[Haseeb Avarakkan]

Check the both cells formats. C50 & BH, both should be in same format.

Ie,

C50 = 50 (formated as general)
BH250 = 50 (formated as text, looks like same as C50, or extra spaces...)

VLOOKUP will give you #N/A in this case.

 

[TheIntern]

Thanks for the quick relpy, the problem was, that I had to transfer the formula to a value. Vlookup doesn't work with a formula, right?

 

[snoopyhr]

Simple extract done 2 ways, it might help you to get on the track....Of course you can nest it with If, iserror or even N/A , this was meant to show you can use 2 functions "Vlookup " and "index / match"

good luck

Excel Workbook
	F	G	H	I	J	K	L
1					Extracted in the same order as master sheet
2	This is master sheet (I think?)				This is sheet 2
3	Inventory No	Qty			Inventory No	-Using index	-Using Vlookup
4	Inv-1	4			Inv-1	4	4
5	Inv-3	10			Inv-3	10	10
6	Inv-10	22			Inv-10	22	22
7	Inv-2	5			Inv-2	5	5
8	Inv-11	55			Inv-11	55	55
9	Inv-12	88			Inv-12	88	88
10	Inv-9	100			Inv-9	100	100
11	Inv-8	55			Inv-8	55	55
12	Inv-5	67			Inv-5	67	67
13	Inv-7	230			Inv-7	230	230
14	Inv-6	4			Inv-6	4	4
15	Inv-4	2			Inv-4	2	2
Sheet4

 

[Haseeb Avarakkan]

VLOKUP will work wih formula. May be your cell formated as text or used a TEXT() formula to get the output in those cells.

 

[TheIntern]

Thanks for your help!

It now works with a formula like that:

=IF(ISNA(VLOOKUP(C2;'sheet1'!$BH$12:$BI$5050;2;FALSE));"";(VLOOKUP(C2;'sheet1'!$BH$12:$BI$5050;2;FALSE)))

I don't really know what I changed...anyway, how can I convert a formula to numbers? Usually an error occurs and I convert text like that.

 

[TheIntern]

I already solved my problem:

=IF(IF(A14="name";F14;"")="";"";VALUE(IF(A14="name";F14;"")))

Thanks again for your help everyone!