£0.00 @

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 ne…

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.

Options

## All Comments

(18) Jump to unreadPost a commentsheet 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.

=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 on Apr 10, 2011 00:23: Posting from iPhone=VLOOKUP(E2,Sheet2!B2:B116,Sheet2!C2:C116,FALSE)

Nope i have posted the formula i tried?

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

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

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))

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

Could be format issue? Try text to columns

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

=VLOOKUP(E2,

Sheet2!B2:B116,Sheet2!C2:C116,FALSE)The bit I highlighted is wrong... It should be :

=VLOOKUP(E2,

Sheet2!B2:C116,2,FALSE)[/quote]Edited By: DragonChris on Apr 10, 2011 10:50