Hi,
I work for a volunteer organisation that sends products on to other organisations. We're trying to find a formula that, when you typed the name of a place we're sending the product to into a table, it would look up that place in a different table and tell you what type of place it is. I thought that LOOKUP would do that, but it isn't working and I'm not sure why. I'm a bit of a beginner using Excel but very keen to learn.
So essentially there's a list of destinations for the product, and what type of destination they are, which looks like this (Table 1):
but is quite a lot longer (about 20 organisations and two types of waste).
Then there's a separate table which looks like this (Table 2):
which is on a different page of the same spreadsheet.
We want to add an extra column to Table 2, which would be called 'Destination Type'. It would take the organisation in the 'Destination' column of table 2, and look for it in the 'destination' column of table 1. It would then check which 'type' that destination corresponds to in Table 1.
The formula we thought would work is this: =LOOKUP([@Destination],Table1[Destinations],Table1[Types]). However it doesn't work - it keeps classifying destinations as the wrong types, and we can't understand why. Please can you give me advice on what formula we should be using? Thank you.
I work for a volunteer organisation that sends products on to other organisations. We're trying to find a formula that, when you typed the name of a place we're sending the product to into a table, it would look up that place in a different table and tell you what type of place it is. I thought that LOOKUP would do that, but it isn't working and I'm not sure why. I'm a bit of a beginner using Excel but very keen to learn.
So essentially there's a list of destinations for the product, and what type of destination they are, which looks like this (Table 1):
| Destination | Type |
| Bin | Waste |
| Charity One | Charity |
| Charity Two | Charity |
| Company One | Commercial |
Then there's a separate table which looks like this (Table 2):
| Date | Destination | Amount sent |
| 10/6/21 | Charity Two | 10 |
| 10/6/21 | Bin | 20 |
| 11/6/21 | Charity One | 5 |
| 12/6/21 | Company One | 7 |
We want to add an extra column to Table 2, which would be called 'Destination Type'. It would take the organisation in the 'Destination' column of table 2, and look for it in the 'destination' column of table 1. It would then check which 'type' that destination corresponds to in Table 1.
The formula we thought would work is this: =LOOKUP([@Destination],Table1[Destinations],Table1[Types]). However it doesn't work - it keeps classifying destinations as the wrong types, and we can't understand why. Please can you give me advice on what formula we should be using? Thank you.
