vlookup xlookup error.xlsx
Screenshot 2021-11-26 184450.png
when i use vlookup or xlookup result is showing correct in cell where i use vlookup or xlookup function after using drag fill option in next cell array cell sequence change and showing wrong result whin I use $ for fix cell range after drag filling in next cell #N/A error occurred in column result is showing correct but in row result is #N/A
Your formula in C15 uses relative references for the rangeinstead of absolute referencesFormula:
Please Login or Register to view this content.and looks like the same for the XLOOKUP functionFormula:Please Login or Register to view this content.
In C15 copied across and down:
=VLOOKUP($B15,$B$3:$Q$9,COLUMN()-1,0)
In C27 copied across and down:
=XLOOKUP($B27,$B$3:$B$9,C$3:C$9)
You need to read up on absolute and relative cell referencing, which you clearly don't yet understand.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
For the xlookup useFormula:Please Login or Register to view this content.
Your comment says
Vlookup result is not correct and also showing #N/A error if I use =vlookup(B15,$B$3:Q$9,2,0) in next row error #N/A error
But that is NOT the formula you are using:
Formula:Please Login or Register to view this content.
If you use $ appropriately in all of your formulas this will work:
Formula:Please Login or Register to view this content.
6StringJazzer I used that but when i drag fill to next row its show #N/A error
Look at the attachment to post #3.
Thanks for the rep, however you should ask your questions here.
You asked:
Yes. Select the range and then press the F4 key repeatedly to cycle through the relative and absolute settings. On some keyboards you may need to hold down the Fn key as well as you do this. Once you have set the range, you can drag copy across and down as I explaied in post #3.Is there shortcut adding $ in lookup
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
I sometimes wonder why I bother to answer when the OP does not even acknowledge my post which is exactly the same as post #5
Unfortunately, some posters lack what I would call 'common courtesy'. It happens to me a lot, especially when I am the first to get my answer in. Sorry you have been ignored.
@meraz
Please don't ignore contributors to your thread - acknowledge all solutions offered, even if they don't meet your requirements. Thanks.
Pepe Le Mokko sorry bro for not acknowledging your post your answer is also worked for me thankyou bro for helpeing and taking time to reply my stupid question.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
You also need to acknowledge Fluff's post. When you've done that, please go and read the forum rules, as you don't appear to have done that yet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks