SOLVED

Excel VLOOKUP (RECHERCHEV) help

%3CLINGO-SUB%20id%3D%22lingo-sub-1380587%22%20slang%3D%22fr-FR%22%3EExcel%20VLOOKUP%20(RECHERCHEV)%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1380587%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%2C%20I%20don't%20know%20if%20there's%20french%20speaking%20people%20here%20so%20to%20be%20sure%20I'll%20write%20the%20question%20in%20both%20languages.%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20I%20do%20not%20know%20if%20there%20are%20French%20here%20so%20I%20put%20the%20question%20in%20French%20and%20English.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5BFR%5D%3C%2FP%3E%3CP%3EBasically%20I%20have%20a%20painting%20with%20first%20names%2C%20date%20of%20birth%2C%20age%20and%20category.%20%3CBR%20%2F%3E%20Age%20is%20calculated%20based%20on%20date%20of%20birth%20with%20dateDIF.%20Don't%20worry%20about%20it.%3C%2FP%3E%3CP%3EOn%20the%20other%20hand%20I%20have%20a%20table%20with%20categories%20but%20on%20another%20sheet.%3C%2FP%3E%3CP%3EI%20have%20to%20use%20the%20RECHERCHEV%20function%20and%20I%20imagine%20INDIRECT%2C%20to%20get%20the%20necessary%20information.%3C%2FP%3E%3CP%3EOnly%20I%20come%20across%20an%20error%20%23REF!%2C%20but%20I%20don't%20really%20understand%20where%20the%20error%20came%20from.%3C%2FP%3E%3CP%3EHere%20the%20result%20should%20be%20%22VETERAN%22.%22%3C%2FP%3E%3CP%3EI'm%20sending%20you%20screens%20from%20the%20screens%20in%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5BENG%5D%3C%2FP%3E%3CP%3ETo%20make%20it%20short%2C%20I%20have%20a%20table%20with%20surnames%2C%20date%20of%20birth%2C%20age%20and%20category.%20%3CBR%20%2F%3E%20The%20ages%20are%20calculated%20using%20the%20DATEDIF%20function.%20No%20problem.%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20the%20different%20categories%20on%20another%20sheet.%3C%2FP%3E%3CP%3EI%20must%20use%20the%20RECHERCHEV%20(VLOOKUP%20in%20english%3F)%20function%20and%20INDIRECT%20I%20think%20to%20get%20the%20information%20needed.%20%3CBR%20%2F%3E%20But%20I%20get%20%23REF!%20error%2C%20but%20I%20don't%20really%20get%20why.%3C%2FP%3E%3CP%3EHere%20the%20result%20should%20be%20%22VETERAN%22%3C%2FP%3E%3CP%3EI%20send%20you%20screens%20of%20the%20sheets.%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Annotation%202020-05-11%20175653.png%22%20style%3D%22width%3A%20338px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190652iB36DB5A4E049D30D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Annotation%202020-05-11%20175653.png%22%20alt%3D%22Annotation%202020-05-11%20175653.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Annotation%202020-05-11%20175906.png%22%20style%3D%22width%3A%20181px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190651iCD6C9ADF28B66407%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Annotation%202020-05-11%20175906.png%22%20alt%3D%22Annotation%202020-05-11%20175906.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1380587%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382124%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLOOKUP%20(RECHERCHEV)%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F663711%22%20target%3D%22_blank%22%3E%40Taytoutay%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20use%20INDIRECT%3F%20The%20formula%20below%20should%20work%20just%20fine%2C%20provided%20that%20the%20ages%20in%20the%22Age%22%20and%20%22Categorie%22%20sheets%20are%20of%20the%20same%20data%20types.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DRECHERCHEV(C2%3BTableau5%5B%23Tout%5D%3B2%3BVRAI)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382395%22%20slang%3D%22fr-FR%22%3ERe%3A%20Excel%20VLOOKUP%20(RECHERCHEV)%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382395%22%20slang%3D%22fr-FR%22%3EHi%2C%20I%20used%20it%20because%20I%20tried%20it%20without%2C%20exactly%20the%20same%20formula%20you've%20given%20me%20but%20I%20got%20the%20same%20error.%20I%20thought%20using%20indirect%20might%20force%20the%20formula%20to%20take%20the%20table%20in%20the%20sheet%20I%20wanted.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20What%20do%20you%20mean%20by%20same%20type%20of%20data%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382511%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLOOKUP%20(RECHERCHEV)%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382511%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F663711%22%20target%3D%22_blank%22%3E%40Taytoutay%3C%2FA%3E%26nbsp%3BBy%20%22data%20types%22%20I%20mean%20that%20if%20the%20lookup%20value%20(i.e.%20C2)%20is%20a%20number%2C%20the%20first%20column%20in%20%22Tableau5%22%20must%20also%20contain%20numbers.%20The%20may%20look%20like%20numbers%2C%20but%20perhaps%20they%20are%20in%20fact%20texts.%20If%20so%2C%20VLOOKUP%20will%20return%20an%20error%20%23NA!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382547%22%20slang%3D%22fr-FR%22%3ERe%3A%20Excel%20VLOOKUP%20(RECHERCHEV)%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382547%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20response%2C%20but%20after%20verification%2C%20it%20seems%20like%20it%20already%20is%20a%20number%20in%20both%20cases.%20And%20the%20formula%20still%20sends%20me%20to%20%22%23REF.%22%20%3CLI-EMOJI%20id%3D%22lia_confused-face%22%20title%3D%22%3Aconfused_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20joining%20the%20file.%20Could%20you%20check%20what's%20wrong%20please%3F%20If%20you%20have%20time%20of%20course.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20changed%20the%20table%20name%20by%20%22cat.%22%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382566%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLOOKUP%20(RECHERCHEV)%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F663711%22%20target%3D%22_blank%22%3E%40Taytoutay%3C%2FA%3E%26nbsp%3BHave%20a%20look%20at%20the%20attached%20file.%20Couldn't%20see%20that%20you%20first%20table%20%26nbsp%3Bis%20also%20a%20structured%20table.%20It%20becomes%20a%20bit%20different%20then%2C%20but%20I%20could%20build%20the%20formula%20just%20by%20pointing%20at%20C2%20and%20it%20automatically%20becomes%26nbsp%3B%5B%40AGE%5D%2C%20which%20means%20%22the%20value%20in%20%3CSTRONG%3Ethis%20row%3C%2FSTRONG%3E%20in%20the%20column%20named%20AGE%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382593%22%20slang%3D%22fr-FR%22%3ERe%3A%20Excel%20VLOOKUP%20(RECHERCHEV)%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382593%22%20slang%3D%22fr-FR%22%3EYou%20solved%20it%20thanks!%20I%20didn't%20know%20about%20the%20%40AGE%20thing%2C%20you%20learn%20me%20something!%20Thanks%20a%20lot!%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382627%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLOOKUP%20(RECHERCHEV)%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F663711%22%20target%3D%22_blank%22%3E%40Taytoutay%3C%2FA%3E%26nbsp%3BGlad%20I%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, I don't know if there's french speaking people here so to be sure I'll write the question in both languages. 

Bonjour, je ne sais pas s'il y a des français ici alors je mets la question en français et en anglais.  

 

[FR]

En gros j'ai un tableau avec prénoms, date de naissance, age et catégorie.
L'âge est calculé en fonction de la date de naissance avec la fonction DATEDIF. Aucun soucis.

Par contre j'ai un tableau avec les catégories mais sur une autre feuille.

Je dois utiliser la fonction RECHERCHEV et j'imagine INDIRECT, pour aller choper les infos nécessaires.

Seulement je tombe sur une erreur #REF!, mais je ne comprends pas vraiment d'où vient l'erreur.

Ici le résultat devrait être "VETERAN".

Je vous envoie des screens des écrans en question.

 

Merci d'avance ! 

 

[ENG]

To make it short, I have a table with surnames, date of birth, age and category.
The ages are calculated using the DATEDIF function. No problem.

I have a table with the different categories on another sheet.

I must use the RECHERCHEV (VLOOKUP in english ?) function and INDIRECT I think to get the informations needed.
But I get #REF! error, but I don't really get why.

Here the result should be "VETERAN"

I send you screens of the sheets.

Thanks in advance !

 

Annotation 2020-05-11 175653.pngAnnotation 2020-05-11 175906.png

 

7 Replies
Highlighted

@Taytoutay 

Why use INDIRECT? The formula below should work just fine, provided that the ages in the"Age" and "Categorie" sheets are of the same data types.

 

=RECHERCHEV(C2;Tableau5[#Tout];2;VRAI)

 

 

 

Highlighted
Hi, I used it because I tried it without, exactly the same formula you've given me but I got the same error. I thought using indirect might force the formula to take the table in the sheet I wanted.

What do you mean by same type of data ?
Highlighted

@Taytoutay By "data types" I mean that if the lookup value (i.e. C2) is a number, the first column in "Tableau5" must also contain numbers. The may look like numbers, but perhaps they are in fact texts. If so, VLOOKUP will return an error #NA!

 

Highlighted

@Riny_van_Eekelen Thanks for your response, but after verification,  it seems like it already is a number in both cases. And the formula still sends me to "#REF".  

 

I'm joining the file. Could you check what's wrong please ? If you have time of course.  

 

I just changed the table name by "cat". 

 

Thanks for your help 

Highlighted
Best Response confirmed by Taytoutay (New Contributor)
Solution

@Taytoutay Have a look at the attached file. Couldn't see that you first table  is also a structured table. It becomes a bit different then, but I could build the formula just by pointing at C2 and it automatically becomes [@AGE], which means "the value in this row in the column named AGE".

 

Highlighted
You solved it thanks ! I didn't know about the @AGE thing, you learn me something ! Thanks a lot !
Highlighted

@Taytoutay Glad I could help.