Excel vlookup help - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Excel vlookup help

£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… Read More
follows2006 Avatar
6y, 2m agoPosted 6 years, 2 months ago
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.
follows2006 Avatar
6y, 2m agoPosted 6 years, 2 months ago
Options

All Comments

(18) Jump to unreadPost a comment
Comments/page:
1 Like #1
Ok so the first product is in sheet 1 in column E and you want the results to show from sheet 2 - where is the matching product code column and which column do you need the corresponding info from?
#2
Hi,

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?
1 Like #3
To do a VLOOKUP is the following syntax (in an empty cell):

=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 number that contains the data. So for example cell A1 has Product Code and cell B1 hasProduct description, you will enter 2 here, 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#4
As above but I'd put it as

=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
#5
Thanks for the advise keep getting value not available error?
#6
formula used:

=VLOOKUP(E2,Sheet2!B2:B116,Sheet2!C2:C116,FALSE)
banned#7
Should have worked so long as you adjusted the values of A2 and B100?
#8
ODB_69
Should have worked so long as you adjusted the values of A2 and B100?


Nope i have posted the formula i tried?
banned#9
Oh yh...no wonder it didn't work

Use my formula but change A to B and B to C
#10
ODB_69
Oh yh...no wonder it didn't work

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))
banned#11
Expand range from 100 to whatever it actually is


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

Could be format issue? Try text to columns
#12
Thanks for all of the help, still cant get it working think i will have to search manually.
banned#13
You have changed sheet 2 to the correct sheet name haven't you?
#14
Cheers ODB yes it is just sheet 2
banned 1 Like #15
Personally I would just write =vlookup(

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
1 Like #16
follows2006
formula used:

=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
#17
Its working! Thank you all so much for your help i really appreciate this.
#18
Np, happy to help.

Post a Comment

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!