Groups

# Excel vlookup help

Hi all hoping someone can help.

I need to do a vlookup, column e contains a product code e.g.1234567.

In sheet 2 i have the relevant product code in Column B what my code is eg.hbis05ss.

I need for excel to lookup what has been ordered (1234567) and tell me what my code is.

I hope that makes sense many thanks in advance.

I need to do a vlookup, column e contains a product code e.g.1234567.

In sheet 2 i have the relevant product code in Column B what my code is eg.hbis05ss.

I need for excel to lookup what has been ordered (1234567) and tell me what my code is.

I hope that makes sense many thanks in advance.

Original Poster

sheet 1 column e is using there order reference

1732937

Sheet 2 contains column b and c b denotes there order reference c is what t relates to B.1733273 C.HPS05SS

Basically i want to have a column inserted that will translate there reference to mine if this makes sense?

=VLOOKUP ( cell_you_want_to_lookup, cell_range_with_corresponding_data, cell_column_number_of_data_to_display, False)

Set "cell_you_want_to_lookup" as $E2 as this is the product code you want to reference.

Set "cell_range_with_corresponding_data" as the cells that contain the same product code and the corresponding data you want to display e.g. Sheet2!$A$2:$B$100. Use $ to lock the cells.

Set "cell_column_number_of_data_to_display" as the column

numberthat contains the data. So for example cell A1 hasProduct Codeand cell B1 hasProduct description, you will enter2here, as the second column has the description you want to display that is cross referenced in the first column.The last part of the statement is set to false. I cba explaining that.

Example below:

=VLOOKUP($E2,Sheet2!$A$2:$B$100,2,False)

Good luck.

Banned

=if(isna(vlookup($E2,Sheet2!$A$2:$B$100,2,False)),"item number not found",vlookup($E2,Sheet2!$A$2:$B$100,2,False))

Edited by:"ODB_69" 10th Apr 2011Original Poster

Original Poster

=VLOOKUP(E2,Sheet2!B2:B116,Sheet2!C2:C116,FALSE)

Banned

Original Poster

Nope i have posted the formula i tried?

Banned

Use my formula but change A to B and B to C

Original Poster

really appreciate your help, i am getting item number not found yet i know the item number is on the list?

Formula used:

=IF(ISNA(VLOOKUP($E2,Sheet2!$B$2:$C$100,2,FALSE)),"item number not found",VLOOKUP($E2,Sheet2!$B$2:$C$100,2,FALSE))

Banned

If that doesn't work search manually for the number and work out what it is

Could be format issue? Try text to columns

Original Poster

Banned

Original Poster

Banned

In the right place and then do it manually...so write that then click on the cell you want it to look at...then highlight area you want it to search so columns b to c of the other sheet. Then stick in 2 and then false (or 0) and you should be done

The bit I highlighted is wrong... It should be :

=VLOOKUP(E2,

Sheet2!B2:C116,2,FALSE)[/quote]Edited by:"DragonChris" 10th Apr 2011Original Poster