Excel formula to find a label corresponding to a code

Youbert Posted messages 8 Status Membre -
Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention - 20 Nov 2015 at 04:29

Hello,
In one Excel sheet, I have a list of codes (column A) corresponding to names (column B).
In another sheet, I have the same codes in column A and columns C, D, E... with other data. Column B is empty, and I would like to enter a formula so that the labels corresponding to the codes appear in this column.
Example
Sheet 1 column A: 75 / column B: PARIS.
Sheet 2 column A: 75 / column B is empty.
I would like to know the formula to make PARIS appear in column B of sheet 2.
Thank you.

Liens connexes:

4 réponses

Réponse 1 / 4

Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention 17 474

Hi.

I just looked at your file. Preliminary questions:

What is the purpose of the comments (identical to the content of the cell) in the first row of each sheet?
Why didn’t you try to write the formula based on the two tutorials I sent you the link to, and the formula whose syntax I indicated? I would have preferred to correct an incorrect formula rather than do everything for you ...

--
Retirement is great! Especially in the West Indies ... :-)
Raymond (INSA, AFPA, CF/R)

Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention 17 474

Furthermore, my formula works perfectly the first time, without any changes, for the first cell B2!
Simply, to ensure that when copied down the range is not altered, you just need to take the precaution of replacing A2:B99 with A$2:B$51.

And if you had defined the name "matrix" for the range Sheet1!A2:B51, the formula in the entire column Sheet2!B:B would have been =VLOOKUP(A:A;matrix;2;0)
https://www.cjoint.com/c/EJAuDTEUacz
I encourage you to read, understand, and apply the two tutorials from my post #1

Réponse 2 / 4

Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention 17 474

Hello Youbert.

This is the simplest application of the VLOOKUP function.
Have you used it before?
https://www.commentcamarche.net/faq/24720-fonction-recherchev-sur-excel
https://forums.commentcamarche.net/forum/affich-37641917-excel-principe-d-edition-de-devis-facture-commande

Best regards.
Retirement is great! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)

Youbert Posted messages 8 Status Membre

Thank you Raymond, I have already used VLOOKUP, but it's old and since then we have a new version of Excel....

Retirement is not on my agenda, but how did you guess that perhaps one day???
More specifically
SHEET1: I have a column A with PARIS and a column B with 75
SHEET2: I have a column A with PARIS and a column B empty.
What formula should I enter in cell B of the SHEET2 to display 75:
VLOOKUP.....????
Thank you

Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention 17 474

If you want to be guided step-by-step in solving your problem, you need to send us your Excel file:

 1) Go to https://www.cjoint.com/ 
 2) Click on [Browse] to select your file (max 8192 Ko)
 3) Scroll down and click on the blue button [Create the Cjoint link] 
 4) After a few seconds, the second page will appear with the link in bold; select it and hit "Copy"
 5) Go back to your discussion on CCM, and in your reply message, hit "Paste".
=>See the guide https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers

The formula will be like =VLOOKUP(A:A,Sheet1!A2:B99,2,0)

Youbert Posted messages 8 Status Membre

https://www.cjoint.com/c/EJArprbIgRg

On the 2nd tab, what is the complete formula to enter starting from VLOOKUP?

Thank you

Réponse 3 / 4

DevAxia Posted messages 37 Status Membre 2

Hello,

If the codes are exactly the same, you can simply use the formula "=" and point to the corresponding cell in tab 1. This will give you a formula in Tab 2 that looks like "='Tab 1'!B1"

If you want to search for the code in the list in column A of tab 1, then use a vertical lookup like this:
"=VLOOKUP(A1,'Tab 1'!A:B,2,FALSE)"

Best regards,
DevAxia

--
Expertise and efficiency, an optimal combination.

Youbert Posted messages 8 Status Membre

Hello,

Thank you for your explanations, but I don't know if it's me who is not good or if it's you who are not very clear because I still can't find the values I'm interested in? -)))

In the Feuil1 tab, there are 2 columns that interest us:
Column A: location codes
Column B: designations.

In the Feuil2 tab, there are also 2 columns that interest us
Column A: it has codes corresponding to those in column A of the Feuil1 tab,
Column B: it is empty.

By pasting the various formulas you gave me, for example:
=VLOOKUP(A1,'Sheet 1'!A:B,2,FALSE), etc... it returns nothing.

Question: What formula should I enter in cell B2 of FEUIL2 for the designation "SOLD - 109 av. Félix Faure" to appear, that is, the label corresponding to the code 75015_06?

Looking forward to hearing from you.

Youbert

JvDo Posted messages 1924 Registration date Status Membre Last intervention 859 > Youbert Posted messages 8 Status Membre

Hello everyone,

you're not far from the truth.
in B2, try:

=VLOOKUP(A2,my__table,2,FALSE)

with the name
my__table defined by =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,5)

best regards

PS: set the format to standard for column B:B

Youbert Posted messages 8 Status Membre > JvDo Posted messages 1924 Registration date Status Membre Last intervention

Thank you.
First, I would like to clarify that I am using Excel 2010.

When I paste =VLOOKUP(A2;my_table;2;FALSE), the result is the formula: =VLOOKUP(A2;my_table;2;FALSE)... instead of "SOLD - 109 av. Félix Faure"

I don't understand what you mean by:
"with the name
my_table defined as =OFFSET(Sheet1!$A$2;;;COUNTA(Sheet1!$A:$A)-1;5)

PS: set the format to standard for column B:B"

Best regards.

JvDo Posted messages 1924 Registration date Status Membre Last intervention 859 > Youbert Posted messages 8 Status Membre

Hello,

you need to define a name my__table with the indicated formula (the 2 underscore characters are a quirk of my keyboard, thanks Asus).
note: you could skip defining a name and place the OFFSET(...) directly in the formula in B2 instead of my__table.

then you should set your column B:B to standard format rather than text format to avoid having your formula appear as ... a formula!! in standard format you will get the result of the formula.

best regards

Youbert Posted messages 8 Status Membre

Thank you, but it is incomprehensible, at least to me.

Réponse 4 / 4

Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention 17 474

Attention, DevAxia, with all your expertise and efficiency, be careful not to mislead the requester!
1) There is little chance that the list in column A is identical in both sheets (otherwise the question wouldn't have been asked...)
2) In the French versions of EXCEL, there are no commas but semicolons within a function to separate arguments...
3) And aside from the commas, what is the difference between your formula and mine?

--
Retirement is great! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)

DevAxia Posted messages 37 Status Membre 2

First of all, regarding your point 3, I was not in competition with you at all; I simply hadn't seen your response when I was writing mine. If our answers are similar, that's great; the requester will surely feel reassured.

Regarding your point 2, indeed, my mistake; I often work in both languages and I got it wrong, but as far as I know, to err is still human.

And about your introduction, there is no need to attack me personally by using my slogan, especially when our goal is to collaborate to help people find answers.

Youbert > DevAxia Posted messages 37 Status Membre

Hello Raymond,
Allow me to say that thanks to CommentCaMarche, I was able to create tables using VLOOKUP, thanks to the Excel file I sent you and that you returned to me with the formula, but I do not have the procedure to create operational tables without starting from the table you sent me and that I save under.

My original question was as follows:
In one Excel sheet I have a list of codes (column A) corresponding to names in column B.
In another sheet, I have the same codes in column A and columns C, D, E... with other data. Column B is empty and I would like to enter a formula so that the labels corresponding to the codes appear in this column.
Example
Sheet 1 column A: 75 / column B: PARIS.
Sheet 2 column A: 75 / column B is empty.

I would like to know the formula so that PARIS appears in column B of sheet 2.

=VLOOKUP(A5;my__table;2;FALSE)
works when I start from your table, but not when I start from a new blank Excel.

Best regards, thank you.

Hubert TOURNIER

yclik Posted messages 3865 Registration date Status Membre Last intervention 1 603

Hello
"my_table" probably refers to the search range.
You need to name the search range in the sheet where the data to be searched is located.

Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention 17 474 > yclik Posted messages 3865 Registration date Status Membre Last intervention

Absolutely!

Excel formula to find a label corresponding to a code

4 réponses

Windows spotlight is not working.

Masked destination layer

Samsung j5 unable to answer

Scam of the suvedene highway

Remove 360 antivirus

My pc's screen is flickering.

Wearline: what do you think?

Eko tank smartwatch

Ibiza sound reviews

Only one of my iphone headphones works. what to do?