I need a quick simple formula for excel data. I want to copy certain data from each "service" line into a specific "service" column (will be in another file, but for convenience I put in this example in the same sheet).
So I need a formula with VLOOKUP or IF or both, I don't know exactly how, but to do the following:
- search for the ID number in column A
- search for the "service name" in column B
- copy the data (column C) into each service column accordingly.
| ID | Service | Data | ||||||
| 1234 | AAA | 4032 | AAA | BBB | CCC | DDD | EEE | |
| 1234 | BBB | 145 | 1234 | vlookup or IF ? | vlookup or IF ? | vlookup or IF ? | vlookup or IF ? | vlookup or IF ? |
| 1234 | CCC | 136,5 | ||||||
| 1234 | DDD | 48 | ||||||
| 1234 | EEE | 56 |
3,000 sats paid
Does this solve your issue?
=IFERROR(VLOOKUP($F2,FILTER($A$2:$C$11,G$1=$B$2:$B$11),3,FALSE),"")Replace
$F2with the column that containsIDin the wide tableReplace
$A$2:$C$11with the full range of your original tableReplace
G$1with the column that contains the service code column heading in the wide tableReplace
$B$2:$B$11with the original service code column(Note, this will only work properly if each
ID, Servicepair in the original data appears at most once.)(Note 2, another suggestion would be to consider using a pivot table for your use case)
This look good but still it give me error. Let me try replacing the separator , with ;
let me know if it works
idk why it gives me error and it prompt to that FILTER. Must be something with locales settings. The formula looks really good, make sense and I see in your screenshot that copied the correct data as I want it.
I will give you the bounty anyways and figure it out starting from this formula. Thanks!
I wonder what version of Excel you are using. Mine was:
Microsoft® Excel® for Microsoft 365 MSO (Version 2411 Build 16.0.18227.20082) 64-bitunbelivable ! On a libre office excel, same file opened and paste the formula it doesn't give error, but it display nothing. wtf is wrong with this ?
tried also on a google sheets with the same file and it gives error "circular dependency"
In that specific picture, the formula is pointing to
$F2but your ID column is onEso it should be$E2. Also, theG$1=...part should sayF. Essentially, your second table is one column shifted to the left from my example.I also tried on google sheet and it should work as long as you fix that error.
TIL the excel formula that contain FILTER is available only for Office 365 MSO. The thing is mi wife have to gather some huge data (like 10k records) from 2 different excels. If I upload those to google sheet, I cannot construct the formula from 2 different files. One file is also getting some data from another file... is a mess.
In the end I made a temporary copy of the data sheet into destination file and working from google sheets.
Yeah I saw it, still the same. Yes, google sheets works !
Yeah must be something related. I tried on another PC with office 2019. Gonna try on mine with libre office.
idk why is returning this error and it prompts to "FILTER".
I am not at my desk, I am using somebody's else PC. gonna try on my other PC with another excel, libre office.
deleted by author
VLOOKUP is so old school.
Use =INDEX(MATCH(…)…)
I don't care which is better, I just need to copy / convert that data from lines to columns, with verifying each ID and service.
(deleted) I didn't see the requirement to look up ID as well. Will return with another post.
Sorry is incomplete. I think I forgot to add something. The ID column is filled with many numbers so the formula also have to find the one corresponding to D3 number ID in the column A (I put the same number to see where should come the data). So some other VLOOKUP should be added.
If you can modify the sheet with your data, I would recommend creating a new column which concatenates ID and SERVICE (but with a delimiter to prevent false lookups)
Then you can use this to generate a simple VLOOKUP, eg:
For those who aren't familiar - the use of the
$sign means that the formula will "stick" to that column (or row) when dragging a formula along or down.The thing is that ID and service are repeated lines too. So ID 1234 could be on 4 or 6 lines, but having different services. This literally switching lines to columns with lookup for 2 references and return the data for each service.
I know I have to make a vlookup + if formula but can't get it working right now.
The formula works for the case you describe - see below (also I fixed the range element, is now
$A:$D)Or are you saying there can be multiple lines for the same combination of ID and Service?
In which case you would like to sum the Data column?
No I don't want to sum data. Only want to copy data from each service line into a service column.
Does this make sense?
By "Drag" I mean the fact that you can click on the bottom right of the cell and "drag" the formula down. This will update the cell references accordingly and display the corresponding values.
Here is a copy of the spreadsheet: https://paste.nostrdev.com/?d557826675410582#BBFDzTe8txYBzY9xhHNfiNvRNtPVZr4kvVACPCrnNfdN
I get formula error. Why did you create column A, just to concatenate column B and C? I don't need exactly that, I need only to search and compare.
I was able to do it in Google Sheets with the following:
=QUERY( A$1:C$6, "SELECT C WHERE A = " & D3 & " AND B = '" & E2 & "'", 0 )In this case the first argument
A$1:C$6could be a range in the same sheet, a different tab, or a different sheet.The second argument has some concatenation, D3 and E2 which are the service names you listed horizontally plus the ID, so you'll need to reference them in the query and these variables will change for each new value horizontally. So the second will reference D3 and F2 instead.
And then last argument is just to inform the query that the data range has a header and it shouldn't be considered in the query.
but this is only a query not a copy, right?
That is correct, the formula/function resolves those values but it doesn't copy, so as long as the reference remains healthy it should be dynamic.