Search on Open Office and error 502
Solved
Nasax
Posted messages
3
Status
Membre
-
Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention -
Raymond PENTIER Posted messages 58546 Registration date Status Contributeur Last intervention -
Hello,
I've been searching for hours on how to perform a lookup with multiple sheets, a real nightmare for me since I'm quite bad at Excel and the fact is that the teacher rushed through this topic. I'm finally trying to seek help, even though I wished I could manage on my own, but now it's becoming impossible.
It's simple, actually, I mainly have a problem with the lookup criteria.
For example, I have an invoice sheet and a catalog sheet; I want to take the designation from the catalog sheet based on the article code, which looks like this in the catalog sheet:
Article Code Designation
A4 = 1 B4 = Food
Here, the catalog table extends from A4 to C17, but I only want to retrieve the designation for now.
The difficulty for me is with A4 because I just need to copy 1 (I think, or maybe that's where I'm going wrong) in A14 of the invoice sheet.
Then for the invoice sheet, it looks like this:
Article Code Designation
A14 = 1 (copied without a formula) B14 (the formula I've typed) = VLOOKUP(A14, catalog.A4:B17, 14, FALSE) (which gives error 502)
I have the same problem with clients where the lookup criterion is the name.
What should I do? If I'm wrong, please try to explain clearly how to make it so that A14 is considered a number like A4 in the catalog so that my formula works. (For me, even when Excel is explained to me, I have trouble understanding.)
I've been searching for hours on how to perform a lookup with multiple sheets, a real nightmare for me since I'm quite bad at Excel and the fact is that the teacher rushed through this topic. I'm finally trying to seek help, even though I wished I could manage on my own, but now it's becoming impossible.
It's simple, actually, I mainly have a problem with the lookup criteria.
For example, I have an invoice sheet and a catalog sheet; I want to take the designation from the catalog sheet based on the article code, which looks like this in the catalog sheet:
Article Code Designation
A4 = 1 B4 = Food
Here, the catalog table extends from A4 to C17, but I only want to retrieve the designation for now.
The difficulty for me is with A4 because I just need to copy 1 (I think, or maybe that's where I'm going wrong) in A14 of the invoice sheet.
Then for the invoice sheet, it looks like this:
Article Code Designation
A14 = 1 (copied without a formula) B14 (the formula I've typed) = VLOOKUP(A14, catalog.A4:B17, 14, FALSE) (which gives error 502)
I have the same problem with clients where the lookup criterion is the name.
What should I do? If I'm wrong, please try to explain clearly how to make it so that A14 is considered a number like A4 in the catalog so that my formula works. (For me, even when Excel is explained to me, I have trouble understanding.)
2 réponses
Hello,
Thank you for your help, my only mistake was actually on 14 which was 2 (still, I don't know how we know it's 1 or 2, anyway), otherwise I did:
VLOOKUP(A14, catalog.A4:B17, 2, 0) and it works perfectly whereas yesterday and Saturday I was convinced that I had done exactly the same operation, I start to believe there’s a bug in Open Office at that point. Finally, as for the '!' instead of the '.' that's how the version of Open Office is, which is why it's fine.
Thank you for your help, my only mistake was actually on 14 which was 2 (still, I don't know how we know it's 1 or 2, anyway), otherwise I did:
VLOOKUP(A14, catalog.A4:B17, 2, 0) and it works perfectly whereas yesterday and Saturday I was convinced that I had done exactly the same operation, I start to believe there’s a bug in Open Office at that point. Finally, as for the '!' instead of the '.' that's how the version of Open Office is, which is why it's fine.
For 1 and 2, it's simple: this number indicates which column of the range contains the information to display
Column A contains the code, and column B contains the description of the item
B is the column number 2 of the lookup range A4:B17
If the range were R4:S17, it would still be column number 2 of the range and the formula would be VLOOKUP(A14, catalog.R4:S17, 2, 0)